Constraints and Schemas in SQL Server 2008

Schemas give us some options. 

Someone posed the question the other day…”Can you have a constraint with the same name multiple times in a database?”  The answer is, of course, yes.  Because schemas are available, you can create constraints now per schema.  Note that in Books Online (BOL) the following verbiage has been changed to include schemas, previously reading “within a database”:

constraint_name

Is the name of a constraint. Constraint names must be unique within the schema to which the table belongs.

Here is an example – note that the constraint name exists more than once in a database:

CREATE TABLE Test1.Table1 (CustId int CONSTRAINT CustIdPK PRIMARY KEY)

GO

CREATE TABLE Test2.Table1 (CustId int CONSTRAINT CustIdPK PRIMARY KEY)

GO

CREATE TABLE Table3 (CustId int CONSTRAINT CustIdPK PRIMARY KEY)

GO

-- This one will fail since Table3 has this constraint name

CREATE TABLE Table4 (CustId int CONSTRAINT CustIdPK PRIMARY KEY)

GO

The same can be said about Foreign Key constraints. Here we create the same FK constraint/name multiple times in the database. Note also that these can cross schemas, no problem.

CREATE TABLE Test1.FK_Table1 (ID int, CustId int

              , CONSTRAINT fkdemo1 FOREIGN KEY (CustId)

              REFERENCES Test1.Table1 (CustId))

GO

CREATE TABLE Test2.FK_Table1 (ID int, CustId int

              , CONSTRAINT fkdemo1 FOREIGN KEY (CustId)

              REFERENCES Test2.Table1 (CustId))

GO

-- This one fails because Test2.FK_Table1 has this constraint name

CREATE TABLE Test2.FK_Table2 (ID int, CustId int

              , CONSTRAINT fkdemo1 FOREIGN KEY (CustId)

              REFERENCES Test1.Table1 (CustId))

GO

-- This one works because dbo has no 'fkdemo1' constraint yet

-- Ok to cross schemas

CREATE TABLE  FK_Table3 (ID int, CustId int

              , CONSTRAINT fkdemo1 FOREIGN KEY (CustId)

              REFERENCES Test1.Table1 (CustId))

GO

On a sidebar, since create default has been deprecated, it’s probably advised to name defaults now more than ever; maybe a good thing, since you can name the default accordingly. Before, when you created a table you might have just added the default in SSMS or Enterprise Manager, and SQL Server simply assigned some klugey long ‘default’ default name. 

CREATE TABLE Default1 (ID int, Value bit constraint Default_Zero DEFAULT (0))

GO

-- Fails - already exists!

CREATE TABLE Default2 (ID int, Value bit constraint Default_Zero DEFAULT (0))

GO

Thanks for reading!
Lee 

------------------------------------ 

You're at PASS and I'm writing some worthless blog entry