Is it a good idea to specify a schema as part of a constraint name?

In my SQL Server bazaars, I group tables by design to help organize their assignment.

When I create constraints for the columns in these tables, I include the schema as part of the name. For example, if I have tables like Com.WebUser and Com.Company :

 PK_Com_WebUser --Primary key constraint FK_Com_WebUser_Com_Company --Foreign key to Com.Company table UQ_Com_WebUser_CompanyID_Username --Unique constraint on CompanyID & Username 

I figured that in the case when I ever had another table with the same name in a different schema, in which the schema in the constraint name would make everything more understandable, but the names are a bit detailed.

Is there a best practice for naming these objects?

+4
source share
4 answers

I think that adding a schema name is good practice for the reason you already mentioned (duplicate table names in schemas), I would not worry about how detailed the name of the constraint is, because you rarely have to refer to these constraints .

+4
source

The possibility is to give each table an alias of a short table, for example

 Com.Customer => cst Com.Company => com Com.WebUser => wus 

and use constraint names like

 PK_wus FK_wus_com UQ_wus_CompanyID_Username 

If new tables are added, give them new unique aliases.

I was working on a project in which column names were prefixes with this table alias

 com_CompanyID com_Name 

and etc.

+2
source

Technically, the restriction refers to the same scheme as the base table. You also cannot reference a constraint without specifying a table. You can see this in the following code snippet:

 CREATE SCHEMA s1; GO CREATE SCHEMA s2; GO CREATE TABLE s1.T(i int CONSTRAINT tpk PRIMARY KEY); GO CREATE TABLE s2.T(i int CONSTRAINT tpk PRIMARY KEY); GO SELECT OBJECT_SCHEMA_NAME(object_id) SchemaName,name,type_desc FROM sys.objects WHERE schema_id IN (SCHEMA_ID('s1'),SCHEMA_ID('s2')); GO 

The only exception is the OBJECT_ID function. There you can refer to a constraint without specifying its base table. But when using this function, you should always specify the scheme in the same way:

 SELECT OBJECT_ID('s1.tpk'),OBJECT_ID('s2.tpk'); 

For all of the above, I believe that the name of the schema in the punctuation name is an unnecessary repetition. So, adhering to the DRY principle, you should not do this.

+2
source

I understand this is an old thread, but I needed an answer to this question, so maybe someone else is doing this ...

Obviously, the same key / index names and control constraint names can indeed be repeated in different schemes in the same database, so I agree with the above comments and I see no reason to add the scheme name as part of the constraint name

eg. The following code works in SQL 2012 and 2008 R2 without errors

 -- create a table in the dbo schema, with primary key CREATE TABLE dbo.Children ( id_Child int IDENTITY(1,1) NOT NULL, ChildName varchar(50) NULL, id_Parent int NOT NULL, CONSTRAINT PK_Children PRIMARY KEY CLUSTERED (id_Child ASC) ) GO -- now an index and a check constraint CREATE NONCLUSTERED INDEX IX_Children_ChildName ON dbo.Children (ChildName ASC) GO ALTER TABLE dbo.Children WITH CHECK ADD CONSTRAINT CK_Children_LongEnough CHECK (len([ChildName])>(3)) GO -- now create another schema CREATE SCHEMA test AUTHORIZATION dbo GO -- an indentical table in the other schema, with a PRIMARY KEY OF THE SAME NAME CREATE TABLE test.Children ( id_Child int IDENTITY(1,1) NOT NULL, ChildName varchar(50) NULL, id_Parent int NOT NULL, CONSTRAINT PK_Children PRIMARY KEY CLUSTERED (id_Child ASC) ) GO -- now an index and a check constraint on the alternate table in another schema, with -- the IDENTICAL NAMES CREATE NONCLUSTERED INDEX IX_Children_ChildName ON test.Children (ChildName ASC) GO ALTER TABLE test.Children WITH CHECK ADD CONSTRAINT CK_Children_LongEnough CHECK (len([ChildName])>(3)) GO 
+2
source

Source: https://habr.com/ru/post/1395674/


All Articles