Constraints renaming

  • Luis Cazares

    SSC Guru

    Points: 183633

    Comments posted to this topic are about the item Constraints renaming

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • karlheinz.ulonska

    Old Hand

    Points: 328

    Hello Luis,I think the naming convention of the FK is a problem.

    First:

    ALTER TABLE [dbo].t1 WITH CHECK ADD CONSTRAINT [t1t2] FOREIGN KEY(c1, c2)

    REFERENCES [dbo].t2 (c1, c2)

    GO

    ALTER TABLE [dbo].t1 CHECK CONSTRAINT [t1t2]

    GO

    EXEC sp_rename 't1t2', 'FK_t1_t2_c1', 'OBJECT'; --works

    EXEC sp_rename 't1t2', 'FK_t1_t2_c2', 'OBJECT'; --at this point [t1t2] does not exists any longer.

    I got the following error message:

    Meldung 15248, Ebene 11, Status 1, Prozedur sp_rename, Zeile 321

    Der @objname-Parameter ist mehrdeutig, oder der beanspruchte Wert von '@objtype' (OBJECT) ist falsch.

    Second:

    If there are two FK on one table to another table I see some problem with your naming convention: Example:

    ALTER TABLE [dbo].t1 WITH CHECK ADD CONSTRAINT [t1t2x] FOREIGN KEY(c1)

    REFERENCES [dbo].t2 (c1)

    GO

    ALTER TABLE [dbo].t1 CHECK CONSTRAINT [t1t2]

    GO

    ALTER TABLE [dbo].t1 WITH CHECK ADD CONSTRAINT [t1t2y] FOREIGN KEY(c2)

    REFERENCES [dbo].t2 (c2)

    GO

    ALTER TABLE [dbo].t1 CHECK CONSTRAINT [t1t2]

    GO

    EXEC sp_rename 't1t2x', 'FK_t1_t2', 'OBJECT'; --works

    EXEC sp_rename 't1t2y', 'FK_t1_t2', 'OBJECT'; --works, but not in the expected way or error

    Third:

    I use the column name only if the FK depends on ONE column, so the second fact is no problem. If the FK depends on more then one columns i only use both table names and count, if there are more then one FK.

    Fourth:

    DECLARE @SQL varchar(600);

    The name column in sys.object is from typ sysname.

    But sysname is a built in datatype limited to 128 Unicode characters.(=nvarchar)

    I miss the check on length of the resulting name

    Think of a veryveryveryvery...longTableName

  • Luis Cazares

    SSC Guru

    Points: 183633

    You're right, those issues might cause a problem. They didn't in my current environment, but I'd be willing to improve the script if you help me with some DDL to create such cases.

    The functionality would be similar to the unique constraint renaming, to correct the first 3 issues, or probably a combination of both approaches.

    For the length option, a different approach should be used to validate that the length is correct.

    Luis C.
    General Disclaimer:
    Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

    How to post data/code on a forum to get the best help: Option 1 / Option 2
  • karlheinz.ulonska

    Old Hand

    Points: 328

    Hello Luis,

    here is the DDL Script.

    x in the comment part shows that I uses a different naming convention than you.

    The cases are named by a to h. (they are not in order, because I need unique constraints for the definition of FKs.

    -- Purpose: The following script renames the constraints with the following naming convention:

    -- a - Primary Keys: PK_TableName

    -- x b - Foreign Keys: FK_TableName_ReferencedTableName_ColumnName (Column name is only included when a table is referenced only by one column)

    -- x c - Foreign Keys: FK_TableName_ReferencedTableName[_Num] (Adds a numeral if more than one FK exists in a table with more than one Column name is referenced)

    -- d - Defaults: DF_TableName_ColumnName

    -- x e - Unique Constraints: UQ_TableName_ColumnName (Column name is only included when a table is referenced only by one column)

    -- x f - Unique Constraints: UQ_TableName[_Num] (Adds a numeral if more than one UQs exist in a table where more than one Column name is referenced)

    -- x g - Check Constraints: CK_TableName_ColumnName (Column name is only included when a table is referenced only by one column)

    -- x h - Check Constraints: CK_TableName[_Num] (Adds a numeral if more Column is checked)

    create table TMaster ( c1 int not null, c2 int not null, c3 int, c4 int, c5 int, c7 int, c8 int)

    GO

    create table Tcase_b ( c1 int, c6 int)

    GO

    create table Tcase_c ( c2 int, c3 int, c4 int, c5 int, c7 int, c8 int)

    GO

    --pk

    ALTER TABLE TMaster ADD CONSTRAINT whateveryouwant PRIMARY KEY (c1)

    GO

    --unique f

    ALTER TABLE TMaster ADD CONSTRAINT UQ2whateveryouwant UNIQUE (c4, c7);

    ALTER TABLE TMaster ADD CONSTRAINT UQ3whateveryouwant UNIQUE (c5, c8);

    GO

    --fk case b

    ALTER TABLE [dbo].Tcase_b WITH CHECK ADD CONSTRAINT FK1whateveryouwant FOREIGN KEY(c1)REFERENCES [dbo].TMaster (c1)

    GO

    --fk case c

    ALTER TABLE [dbo].Tcase_c WITH CHECK ADD CONSTRAINT FK2whateveryouwant FOREIGN KEY(c4, c7)REFERENCES [dbo].TMaster (c4, c7)

    GO

    ALTER TABLE [dbo].Tcase_c WITH CHECK ADD CONSTRAINT FK3whateveryouwant FOREIGN KEY(c5, c8)REFERENCES [dbo].TMaster (c5, c8)

    GO

    --default d

    ALTER TABLE TMaster add CONSTRAINT Dwhateveryouwant DEFAULT 1000 FOR c5

    GO

    --unique e

    ALTER TABLE Tcase_b ADD CONSTRAINT UQ4whateveryouwant UNIQUE (c6);

    GO

    -- cc g

    ALTER TABLE Tcase_b ADD CONSTRAINT CC1whateveryouwant CHECK (c6 >= 1 )

    GO

    --cc h

    ALTER TABLE Tcase_c ADD CONSTRAINT CC2whateveryouwant CHECK (c7 >= 1 AND c8 <= 100000000)

    GO

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Thanks for the script. It provides some ideas worth looking at.

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply