Export Primary, Unique, Foreign Key and Default Constraints in SQL Server

Introduction

Moving data and table schema with constraints in SQL server from one server to another server where the users are not allowed to restore the completed database, moving a change set (few table data, Primary keys, Foreign Keys and default values) from one server to other server is always challenging and required lot of efforts to prepare the SQL scripts.

In this article, I have created some queries that would help developers and makes their life easy to some extent.
Import and export data utility does not create any key and default constraints while creating tables in destination database.

Below are some useful queries that would help you..

Enable and Disable Identity Insert

This query will helps you to get the tables which have identity column in existing database and you can easily disable the identity insert values after running the output of the below query:

SELECT 'SET IDENTITY_INSERT ' + TABLE_NAME + ' ON'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME

Output

SET IDENTITY_INSERT Table1 ON
SET IDENTITY_INSERT Table3 ON

Once your identity insert is disabled, you can insert your data and enable the constraint back to normal through the below query:

SELECT 'SET IDENTITY_INSERT ' + TABLE_NAME + ' OFF'
FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMNPROPERTY(object_id(TABLE_NAME), COLUMN_NAME, 'IsIdentity') = 1
ORDER BY TABLE_NAME

Output

SET IDENTITY_INSERT Table1 OFF
SET IDENTITY_INSERT Table3 OFF 

Primary Key Constraints

This query helps you to generate the query to create primary keys from existing database. Now it’s fun to create primary key on new database.

SELECT distinct 'ALTER TABLE ' + colinfo.Table_name + ' WITH CHECK ADD CONSTRAINT '
+ colinfo.CONSTRAINT_NAME +' PRIMARY KEY CLUSTERED ('
+  (SELECT stuff((select ',' + column_name  from   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
WHERE CONSTRAINT_NAME =colinfo.CONSTRAINT_NAME  FOR XML PATH('')),1,1,'')) + ')'
 
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE colinfo inner join
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblC on tblC.CONSTRAINT_NAME = colinfo.CONSTRAINT_NAME 
and tblC.CONSTRAINT_SCHEMA = colinfo.CONSTRAINT_SCHEMA
WHERE tblC.CONSTRAINT_TYPE = 'PRIMARY KEY'

Note: Inner query gets the columns name of composite key.

Output

ALTER TABLE ActiveSubscriptions WITH CHECK ADD CONSTRAINT PK_ActiveSubscriptions PRIMARY KEY CLUSTERED (ActiveID) 
ALTER TABLE CachePolicy WITH CHECK ADD CONSTRAINT PK_CachePolicy PRIMARY KEY CLUSTERED (CachePolicyID)
ALTER TABLE ChunkData WITH CHECK ADD CONSTRAINT PK_ChunkData PRIMARY KEY CLUSTERED (ChunkID)
ALTER TABLE ChunkSegmentMapping WITH CHECK ADD CONSTRAINT PK_ChunkSegmentMapping PRIMARY KEY CLUSTERED (ChunkId,SegmentId)

Unique Key Constraints

The above section was for the primary and below query will help you to get the unique key:

SELECT distinct 'ALTER TABLE ' + colinfo.CONSTRAINT_SCHEMA + '.' + colinfo.TABLE_NAME
 +' ADD CONSTRAINT ' + colinfo.CONSTRAINT_NAME +' UNIQUE ( '
 +(select stuff((select ',' + COLUMN_NAME  from   INFORMATION_SCHEMA.KEY_COLUMN_USAGE
 WHERE CONSTRAINT_NAME =colinfo.CONSTRAINT_NAME  FOR XML PATH('')),1,1,''))
  +')'
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE colinfo inner join 
INFORMATION_SCHEMA.TABLE_CONSTRAINTS tblC on tblC.CONSTRAINT_NAME = colinfo.CONSTRAINT_NAME 
and tblC.CONSTRAINT_SCHEMA = colinfo.CONSTRAINT_SCHEMA
WHERE tblC.CONSTRAINT_TYPE = 'UNIQUE'

Output

ALTER TABLE dbo.Schedule ADD CONSTRAINT IX_Schedule UNIQUE ( Name,Path)
ALTER TABLE dbo.Contact ADD CONSTRAINT IX_Schedule UNIQUE ( ID)

Default Constraints

This query will retrieve the default constraints from database and generate query to create constraints.

SELECT 'ALTER TABLE ' + s.name +'.' + t.name +' ADD CONSTRAINT ' + d.name + ' default ' +
  d.definition + ' FOR [' + c.name + ']'
  
 FROM sys.default_constraints d
 INNER JOIN sys.columns c ON d.parent_object_id = c.object_id
  AND d.parent_column_id = c.column_id
 INNER JOIN sys.tables t ON t.object_id = c.object_id
 INNER JOIN sys.schemas s ON s.schema_id = t.schema_id

Output

ALTER TABLE dbo.DBUpgradeHistory ADD CONSTRAINT DF__DBUpgrade__DateT__75F77EB0 default (getdate()) FOR [DateTime] 
ALTER TABLE dbo.Subscriptions ADD CONSTRAINT DF__Subscript__Repor__77DFC722 default ((0)) FOR [ReportZone]
ALTER TABLE dbo.Notifications ADD CONSTRAINT DF__Notificat__Repor__78D3EB5B default ((0)) FOR [ReportZone]

Foreign Key Constraints

This generates the query to create foreign key. If you would like to do this only for the selected tables, you can enhance this by adding where clause and table name is available in the ‘INFORMATION_SCHEMA.KEY_COLUMN_USAGE’.

Select distinct 'ALTER TABLE ' + ReferencingConstraint.TABLE_NAME +'
ADD CONSTRAINT ' + FK.CONSTRAINT_NAME + ' FOREIGN KEY (' +
(select stuff((select ',' + column_name  from   INFORMATION_SCHEMA.KEY_COLUMN_USAGE colInfo
where colInfo.CONSTRAINT_NAME =FK.CONSTRAINT_NAME  FOR XML PATH('')),1,1,''))
 +')
    REFERENCES ' + PK_cons.TABLE_NAME + ' ('+
    (select stuff((select ',' + column_name  from   INFORMATION_SCHEMA.KEY_COLUMN_USAGE colInfo
where colInfo.CONSTRAINT_NAME =FK.UNIQUE_CONSTRAINT_NAME  FOR XML PATH('')),1,1,''))
 +')' 
    + ' ON DELETE '  + fk.UPDATE_RULE +
     ' ON UPDATE ' +FK.DELETE_RULE   
from   INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS FK
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencingConstraint
ON FK.CONSTRAINT_SCHEMA = ReferencingConstraint.CONSTRAINT_SCHEMA
AND FK.CONSTRAINT_NAME = ReferencingConstraint.CONSTRAINT_NAME
inner join INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS PK_cons
ON FK.CONSTRAINT_SCHEMA = ReferencingConstraint.CONSTRAINT_SCHEMA
AND FK.UNIQUE_CONSTRAINT_NAME = PK_cons.CONSTRAINT_NAM

Output

ALTER TABLE ActiveSubscriptions   ADD CONSTRAINT FK_ActiveSubscriptions_Subscriptions FOREIGN KEY (SubscriptionID)_
REFERENCES Subscriptions (SubscriptionID) ON DELETE NO ACTION ON UPDATE CASCADE
ALTER TABLE CachePolicy   ADD CONSTRAINT FK_CachePolicyReportID FOREIGN KEY (ReportID)_
REFERENCES Catalog (ItemID) ON DELETE NO ACTION ON UPDATE CASCADE
ALTER TABLE Catalog   ADD CONSTRAINT FK_Catalog_CreatedByID FOREIGN KEY (CreatedByID)_
REFERENCES Users (UserID) ON DELETE NO ACTION ON UPDATE NO ACTION