NAMING CONSTRAINTS VS. SYSTEM-GENERATED CONSTRAINTS

When defining constraints on a table, it's good practice to give the
constraints names. Otherwise, SQL Server will create system-generated 
names
for the constraints. When generating DDL without constraint names (when
the DDL is applied on more than one database), the constraint names are
typically not the same.

After generating the schema on a database, it's good practice to
generate a detailed constraint list to compare against a known valid 
constraint
list for a particular schema build. This is especially beneficial when
the database is very large.

The following is a script that demonstrates the differences between
naming constraints, not naming constraints, and system-generated 
constraint
names that change for the same re-created table.

CREATE TABLE Parent
(pkey1 INT NOT NULL
       CONSTRAINT pk_Parent PRIMARY KEY (pkey1))
GO
CREATE TABLE ConstraintName
(Pkey INT NOT NULL
 CONSTRAINT pk_CnstNm primary key,
 Parent_pkey1 INT NOT NULL,
col1  INT NULL
 CONSTRAINT ck_CnstNm_col1 CHECK  (col1 IN ( 'a','b' ) )
 CONSTRAINT df_CnstNm_col1 DEFAULT 1,
CONSTRAINT fk_Parent_CnstNm FOREIGN KEY (Parent_pkey1)
       REFERENCES Parent (pkey1)
)
GO
exec sp_helpconstraint ConstraintName
GO
DROP TABLE ConstraintName
GO
CREATE TABLE ConstraintName
(Pkey INT NOT NULL
       primary key,
 Parent_pkey1 INT NOT NULL
       FOREIGN KEY (Parent_pkey1) REFERENCES PARENT(pkey1),
 col1  INT NULL
       CHECK  (col1 IN ( 'a','b' ) )
       DEFAULT 1
)
GO
exec sp_helpconstraint ConstraintName
GO
DROP TABLE ConstraintName
GO
CREATE TABLE ConstraintName
(Pkey INT NOT NULL
       primary key,
 Parent_pkey1 INT NOT NULL
       FOREIGN KEY (Parent_pkey1) REFERENCES PARENT(pkey1),
 col1  INT NULL
       CHECK  (col1 IN ( 'a','b' ) )
       DEFAULT 1
)
GO
exec sp_helpconstraint ConstraintName
GO
DROP TABLE ConstraintName
GO
DROP TABLE Parent
GO

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