User-friendly names for constraints

Question

User-friendly names for constraints

gacanepa

Old Hand

Points: 352
More actions
May 20, 2017 at 9:06 pm

#337940

I just noticed that when you create a column with a default value, a constraint is added to the table. However, as it is the case with primary keys, it is not given a user-friendly name:

So the question is, how can I set a constraint on a column during table creation (be it a default value or another type) and give it a user-friendly name such as DF_IsLate? Is there any use for this or should I just not worry about it?
Thanks in advance.

Viewing 9 posts - 1 through 8 (of 8 total)

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

DesNorton SSC-Insane Points: 24284 More actions · Answer 1

You can provide a user-friendly name at creation time by explicitly giving it a name.
Note that I have included the table name in the constarint name. This is because all constraint names in the database must be unique.
CREATE TABLE ClientRequirements ( RequestID INT IDENTITY(1,1) NOT NULL , ClientID INT NOT NULL , SubmitDate DATETIME NOT NULL CONSTRAINT DF_ClientRequirements_SubmitDate DEFAULT (GETDATE()) , LastUpdate DATETIME NOT NULL CONSTRAINT DF_ClientRequirements_LastUpdate DEFAULT (GETDATE()) , IsLate BIT NOT NULL CONSTRAINT DF_ClientRequirements_IsLate DEFAULT (0) , CONSTRAINT PK_ClientRequirements_ClientID PRIMARY KEY (ClientID) );

How to post data/code on a forum to get the best help.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

DesNorton - Sunday, May 21, 2017 1:33 AM
This is because all constraint names in the database must be unique.

Indeed they must. Be careful when creating temp tables with named constraints, because while SQL Server will uniquefy the table name, it won't do the same with the constraint name, and you'll get an error if two processes create the same temp table at the same time. I recommend the use of system-generated constraint names on temp tables.

John

Lowell SSC Guru Points: 323506 More actions · Answer 3

I've written cleanup procedure that generate the sp_rename command for what i consider the the friendly name., so if the constraint doesn't match my expected name, it generates a command that i can review and decide to execute or not.

I've done that with everything...indexes,FK and check constraints, defaults, etc.

--pattern is DF__{shrunktablename}__{ShrunkColumnName(s)} SELECT QUOTENAME(SCHEMA_NAME(tabz.SCHEMA_ID)) + '.' + QUOTENAME(tabz.name) AS QualifiedObjectName, SCHEMA_NAME(tabz.SCHEMA_ID) AS SchemaName, tabz.name AS TableName, colz.name AS ColumnName, defz.definition AS ConstraintDefinition, defz.name AS ConstraintName, 'DF__' + REPLACE(REPLACE(REPLACE(tabz.name,' ',''),'-',''),'_','') + '__' + REPLACE(REPLACE(REPLACE(REPLACE(colz.name,' ',''),'-',''),'_',''),',','_') AS SuggestedConstraintName, CASE WHEN defz.name = 'DF__' + REPLACE(REPLACE(REPLACE(tabz.name,' ',''),'-',''),'_','') + '__' + REPLACE(REPLACE(REPLACE(REPLACE(colz.name,' ',''),'-',''),'_',''),',','_') THEN '' ELSE 'EXEC [sys].[sp_rename] ''' + QUOTENAME(SCHEMA_NAME(tabz.SCHEMA_ID)) + '.' + quotename(defz.name) +''',''' + 'DF__' + REPLACE(REPLACE(REPLACE(tabz.name,' ',''),'-',''),'_','') + '__' + REPLACE(REPLACE(REPLACE(REPLACE(colz.name,' ',''),'-',''),'_',''),',','_') + '''' END AS RenameCommand, defz.* FROM sys.default_constraints defz INNER JOIN sys.tables tabz ON defz.parent_object_id = tabz.OBJECT_ID INNER JOIN sys.columns colz ON tabz.OBJECT_ID = colz.OBJECT_ID AND defz.parent_column_id = colz.column_id;

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Luis Cazares SSC Guru Points: 183698 More actions · Answer 4

I wrote a script some time ago and shared it in this site. http://www.sqlservercentral.com/scripts/constraints/133573/
It's not perfect, but works with most options. It works for Primary Keys, Foreign Keys, Defaults, Unique Constraints & Check Constraints.

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

gacanepa Old Hand Points: 352 More actions · Answer 5

Thank you everyone for your help! I appreciate you taking the time to share your SQL wisdom with me.

Chris Wooding SSCarpal Tunnel Points: 4530 More actions · Answer 6

Regarding your 2nd question "should I worry about it", I'd say it's definitely worth giving them sensible names if you have copies of the database for development, QA, etc. and you want to generate a script from one to apply to another. The auto-generated names will be different on the different servers which would make comparing databases, version control and scripting difficult.

RonKyle SSC-Dedicated Points: 31539 More actions · Answer 7

I also take the time to make them user friendly names except in the case (as has already been pointed out) in temp tables. Definitely a good habit to get into.

ScottPletcher SSC Guru Points: 100950 More actions · Answer 8

I prefer the format "<table_name>__DF_<column_name>". I think it's more useful to prefix constraints with the table name rather than "DF_". Just because MS did it that way doesn't mean it's necessarily the best way :).

SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.