Good idea to specify schema as part of constraint name?

Technically the constraint belongs to the same schema as it's base table. You also cannot reference the constraint without specifying the table as well. You can see that 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. In there you can reference a constraint without specifying it's base table. But when using this function you should always specify the schema as well anyway:

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

Because of all the above I consider putting the schema name into the contraint name superfluous repetition. So, adhering to the DRY principle, you should not do it.

I realise this is an old thread, but I needed an answer to this, so maybe someone else does too...

Evidently the same key / index names and check constraint names can indeed be repeated in different schemas in the same database, so I agree with the above comments, and don't see the point of adding the schema name as part of the constraint name

E.g. the following code works in SQL 2012 and 2008 R2 with no 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 

A possibility is to give each table a short table alias, e.g.

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

and to use constraint names like

PK_wus FK_wus_com UQ_wus_CompanyID_Username 

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

I worked on a project where the column names were prefixed with this table alias

com_CompanyID com_Name 

etc.