Sql-server – Missing PK/FK after database migration

foreign keyprimary-keysql-server-2008

We recently had a server migration. A new server is now the PROD server, and what used to be the PROD server is now the staging environment.

Apparently, the data was transferred correctly, but the database is missing its PK/FK contraints. Luckily, the database hasn't been touched yet as the connected application is still in migration, which means that if the PK/FK constraints are now enabled, there shouldn't be any issues because no rows were created/altered/deleted.

We stil have the original database (which is now staging). The structure is the same, but the content has already changed numerous times during dev testing.

Is there a way in which I can keep the current PROD database, and add the PK/FK constraints to it (based on the staging database)?

Extra info:

  • we have no backups readily available unfortunately, else I would've fixed it that way
  • Using SQL Server 2008 via SSMS
  • I'm not the one who performed the data migration. It is office procedure to move databases by using .bak files, but I fear someone used the import/export option, hence losing the PK/FK constraints.

Best Answer

Script out the constraints from the staging database and apply to the problem database. Lots of options, just make sure you have backups of staging and prod before you start:

  1. Manually, one by one. Right click the objects in SSMS and opting to 'Script'.
  2. Manually, all together (with a bit of editing). Right click database in SSMS and Tasks / Generate Scripts / Select 'Tables'. Under Scripting Options / Advanced include PK/FK. The script created will include table definitions, you'll have to edit to leave just PK/FK.
  3. Use a schema comparison tool to sync staging to production.
  4. Use a script to generate the ALTER statements for you.

    SELECT
        *
        , 'ALTER TABLE ['+ FKTableSchema +'].[' + FKTableName + '] DROP CONSTRAINT ' 
            + ForeignKeyName AS FKDrop
        , 'ALTER TABLE ['+ FKTableSchema +'].[' +FKTableName + '] ADD CONSTRAINT ' 
            + ForeignKeyName + ' FOREIGN KEY(' +
          FKColumnList +') REFERENCES ['+ PKTableSchema+'].['
            + PKTableName + '] ('+PKColumnList +')' AS FKCreate
        , 'ALTER TABLE ['+ PKTableSchema +'].[' +PKTableName + '] DROP CONSTRAINT ' 
            + PrimaryKeyName AS PKDrop
        , 'ALTER TABLE ['+PKTableSchema +'].[' +PKTableName + '] ADD CONSTRAINT ' 
            + PrimaryKeyName + ' PRIMARY KEY('+PKColumnList+')' AS PKCreate
    FROM
        (
        SELECT DISTINCT
            FKTableSchema = 
                (
                SELECT DISTINCT 
                    table_schema 
                FROM 
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE 
                    constraint_name=rc.constraint_Name
                ),
            FKTableName = 
                (
                SELECT DISTINCT 
                    table_Name 
                FROM 
                    INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
                WHERE 
                    constraint_name=rc.constraint_Name
                    ),
            rc.constraint_name AS ForeignKeyName,
            FKColumnList = 
                (
                SELECT 
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList' 
                FROM
                    (
                    SELECT Column_Name + ',' FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE constraint_name=rc.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                ),
            cu.table_schema AS PKTableSchema,
            cu.table_name AS PKTableName,
            cu.constraint_Name AS PrimaryKeyName,
            PKColumnList = 
                (
                SELECT 
                    left(t.column_name,len(t.column_name)-1) AS 'ColumnList' 
                FROM
                    (
                    SELECT 
                        Column_Name + ',' 
                    FROM 
                        INFORMATION_SCHEMA.KEY_COLUMN_USAGE
                    WHERE 
                        constraint_name=cu.constraint_name
                    FOR XML PATH('')
                    ) AS t(column_Name)
                )
        FROM
            INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS cu
        INNER JOIN
            INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS rc
        ON  rc.Unique_Constraint_name= cu.Constraint_name
    ) AS tab
Related Question