November 2, 2015 at 3:19 pm
Comments posted to this topic are about the item Constraints renaming
November 18, 2015 at 4:17 am
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
November 18, 2015 at 7:03 am
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.
November 18, 2015 at 8:37 am
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
November 23, 2015 at 2:05 pm
Thanks for the script. It provides some ideas worth looking at.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply