Skip to content

Dirceu Resende

DBA SQL Server and BI Analyst (PowerBI, SSAS, SSIS, SSRS)

  • Consultancy
    • BI Consulting
    • Power BI Consulting
    • SQL Server Consulting
  • File
  • Series
    • Certification
    • Security and Audit
    • Performance tuning
    • What has changed in T-SQL?
    • Data Protection
  • Jobs
  • Data Platform Events
  • About
  • Contact

Other Languages

Subscribe to a blog by email

Enter your email address to subscribe to this blog and receive notifications of new publications by email.

Join 536 other subscribers

Blog Views

1.645.448 views

Categories

  • Apache / .htaccess (9)
  • Database (307)
    • MySQL / MariaDB (4)
    • Oracle (8)
    • SQL Server (293)
      • Audit (15)
      • Azure (2)
      • CLR (53)
      • Query Development (83)
      • DMVs and Catalog Views (31)
      • Errors (22)
      • Tools (12)
      • Data Formatting and Validation (23)
      • Little Known Features (19)
      • Hacks (17)
      • Easy (30)
      • File Handling (13)
      • Maintenance (80)
      • Monitoring (35)
      • What not to do (7)
      • OLE Automation (19)
      • Performance tuning (22)
      • Python (1)
      • Safety (39)
      • SQL Server Agent (11)
  • Business Intelligence (BI) (31)
    • Analysis Services (SSAS) (7)
    • Microsoft (7)
    • Power BI (12)
    • Reporting Services (SSRS) (8)
  • Career and Courses (13)
  • Career, Courses and Certifications (28)
  • Cell Phones (1)
  • Events and Lectures (63)
  • Programme (57)
    • C # (CSharp) (30)
    • CSS (1)
    • ERP (1)
    • javascript (1)
    • PHP (17)
    • Powershell / CMD (8)
    • SQLCLR (4)
  • Uncategorized (10)
  • SEO (4)
  • Virtualization (5)

Microsoft MVP Data Platform

My Certifications

Training

Posts Archive

Recent Posts

  • Black Friday discounts on SQL Server Trainings (Buy my kkkkk course) November 27th, 2020
  • SQL Server - The “new” GREATEST and LEAST functions November 27th, 2020
  • SQL Server - How to know the date of a user's last login November 9th, 2020
  • Azure in Free Practice # 07 - Administering Databases in Azure November 5th, 2020
  • Analysis Services - An error occurred while opening the model on the workspace database. Reason: An unexpected error occurred (file 'tmcachemanager.cpp', function 'TMCacheManager :: CreateEmptyCollectionsForAllParents') November 5th, 2020
  • October 6th, 2015
  • 3
  • Database DMVs and Catalog Views Maintenance SQL Server

How to identify, delete, and recreate Foreign Keys (FK) from a table in SQL Server

Views: 6.671
Reading Time: 7 minutes

Hello people,
Good Morning!

Introduction

In this post I will demonstrate to you how to identify, delete and recreate Foreign Keys (FK) from a table in SQL Server. We often need to delete a table or just change a column, but the bank sends us an error message stating that there is a foreign key constraint for that column, making it impossible for the DBA to make the requested change.

Msg 5074, Level 16, State 1, Line 57
The object 'PK__Clientes__3214EC07F3BD01EC' is dependent on column 'Id'.
Msg 5074, Level 16, State 1, Line 57
The object 'FK_Cliente_Telephone' is dependent on column 'Id'.
Msg 5074, Level 16, State 1, Line 57
The object 'FK_Cliente_Endereco' is dependent on column 'Id'.
Msg 4922, Level 16, State 9, Line 57
ALTER TABLE ALTER COLUMN Id failed because one or more objects access this column.

To resolve this issue, you must remove the FK contraints, make the change to the table, and then create again. When the table has many references, this task becomes a little laborious, especially if multiple tables are involved.

To facilitate this activity, which is very common on a daily basis, I decided to create and make available to you, a Stored Procedure that checks the FK's in the SQL Server catalog views and brings the complete listing.

Important: For technical reasons, SQL Server allows you to create Foreign Keys only between objects in the same database.

Creating the test mass

To demonstrate the use of this procedure, I created this simple script so that you generate a mass of tests and can test the SP.

Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
IF (OBJECT_ID('dbo.Clientes') IS NOT NULL) DROP TABLE dbo.Clientes
CREATE TABLE dbo.Clientes (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Nome VARCHAR(50)
)
 
INSERT INTO dbo.Clientes
SELECT 'Dirceu'
UNION ALL
SELECT 'Patricia'
UNION ALL
SELECT 'Lucas'
UNION ALL
SELECT 'Leandro'
UNION ALL
SELECT 'Richardson'
 
 
IF (OBJECT_ID('dbo.Enderecos') IS NOT NULL) DROP TABLE dbo.Enderecos
CREATE TABLE dbo.Enderecos (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Id_Cliente INT,
    Ds_Endereco VARCHAR(50)
)
 
ALTER TABLE dbo.Enderecos ADD CONSTRAINT FK_Cliente_Endereco FOREIGN KEY (Id_Cliente) REFERENCES dbo.Clientes(Id)
 
 
INSERT INTO dbo.Enderecos (Id_Cliente, Ds_Endereco)
SELECT 1, 'Endereço Teste Cliente 1'
UNION ALL
SELECT 2, 'Endereço Teste Cliente 2'
UNION ALL
SELECT 3, 'Endereço Teste Cliente 3'
        
 
 
 
IF (OBJECT_ID('tempdb..dbo.Telefones') IS NOT NULL) DROP TABLE dbo.Telefones
CREATE TABLE dbo.Telefones (
    Id INT IDENTITY(1,1) PRIMARY KEY,
    Id_Cliente INT,
    Nr_Telefone VARCHAR(11)
)
 
ALTER TABLE dbo.Telefones ADD CONSTRAINT FK_Cliente_Telefone FOREIGN KEY (Id_Cliente) REFERENCES dbo.Clientes(Id)
 
 
INSERT INTO dbo.Telefones (Id_Cliente, Nr_Telefone)
SELECT 1, '27999998888'
UNION ALL
SELECT 4, '27999997777'
UNION ALL
SELECT 5, '27999996666'
 
 
IF (OBJECT_ID('dbo.Teste1') IS NOT NULL) DROP TABLE dbo.Teste1
CREATE TABLE dbo.Teste1 (
    Cod1 INT NOT NULL,
    Cod2 int NOT NULL,
    Cod3 int NOT NULL,
    Nome VARCHAR(200)
)
 
IF (OBJECT_ID('dbo.Teste2') IS NOT NULL) DROP TABLE dbo.Teste2
CREATE TABLE dbo.Teste2 (
    Codigo1 INT NOT NULL,
    Codigo2 int NOT NULL,
    Codigo3 int NOT NULL,
    Nome VARCHAR(200)
)
 
ALTER TABLE dbo.Teste2 ADD CONSTRAINT [PK_Teste2] PRIMARY KEY (Codigo1, Codigo2, Codigo3)
ALTER TABLE dbo.Teste1 ADD CONSTRAINT [FK_Teste1] FOREIGN KEY (Cod1, Cod2, Cod3) REFERENCES dbo.Teste2(Codigo1, Codigo2, Codigo3)

How to Remove FK's Referencing a Table

With the code below, you can create the SP mentioned in the topic, which allows easy viewing of the FK's that reference a certain table, and already informs the script for removing and creating that FK:

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
CREATE PROCEDURE [dbo].[stpRecria_FK]
    @Database [sysname],
    @Objeto [sysname] = NULL,
    @Schema [sysname] = 'dbo'
WITH EXECUTE AS CALLER
AS
BEGIN
    
    
    DECLARE @Db_Id sysname = (SELECT database_id FROM sys.databases WHERE name = @Database)
    DECLARE @query VARCHAR(MAX)
 
    
    SET @query = '
    SELECT
        FK.name AS Ds_Nome_FK,
        schema_ori.name + ''.'' + objeto_ori.name AS Ds_Objeto,
        coluna_ori.name AS Ds_Coluna,
        schema_dest.name + ''.'' + objeto_dest.name AS Ds_Objeto_Referencia,
        coluna_dest.name AS Ds_Coluna_Referencia,
        ''ALTER TABLE [' + @Database + '].['' + schema_ori.name + ''].['' + objeto_ori.name + ''] DROP CONSTRAINT ['' + FK.name  + '']'' AS Dropar_FK,
        ''ALTER TABLE [' + @Database + '].['' + schema_ori.name + ''].['' + objeto_ori.name + ''] ADD CONSTRAINT ['' + FK.name + ''] FOREIGN KEY ('' + coluna_ori.name + '') REFERENCES [' + @Database + '].['' + schema_dest.name + ''].['' + objeto_dest.name + ''] ('' + coluna_dest.name + '')'' AS Criar_FK
    FROM
        [' + @Database + '].sys.foreign_keys                    AS FK            WITH(NOLOCK)
        JOIN [' + @Database + '].sys.foreign_key_columns        AS FK_Coluna    WITH(NOLOCK)    ON FK.object_id = FK_Coluna.constraint_object_id
    
        JOIN [' + @Database + '].sys.objects                    AS objeto_ori    WITH(NOLOCK)    ON FK.parent_object_id = objeto_ori.object_id
        JOIN [' + @Database + '].sys.objects                    AS objeto_dest    WITH(NOLOCK)    ON FK.referenced_object_id = objeto_dest.object_id
 
        JOIN [' + @Database + '].sys.schemas                    AS schema_ori    WITH(NOLOCK)    ON objeto_ori.schema_id = schema_ori.schema_id
        JOIN [' + @Database + '].sys.schemas                    AS schema_dest    WITH(NOLOCK)    ON FK.schema_id = schema_dest.schema_id
    
        JOIN [' + @Database + '].sys.columns                    AS coluna_ori    WITH(NOLOCK)    ON FK_Coluna.parent_object_id = coluna_ori.object_id AND FK_Coluna.parent_column_id = coluna_ori.column_id
        JOIN [' + @Database + '].sys.columns                    AS coluna_dest    WITH(NOLOCK)    ON FK_Coluna.referenced_object_id = coluna_dest.object_id AND FK_Coluna.referenced_column_id = coluna_dest.column_id'
 
 
    IF (NULLIF(LTRIM(RTRIM(@Objeto)), '') IS NOT NULL)
    BEGIN
 
        SET @query = @query + '
    WHERE
        objeto_dest.name = ''' + @Objeto + ''''
 
    END
    
 
    IF (NULLIF(LTRIM(RTRIM(@Schema)), '') IS NOT NULL)
    BEGIN
 
        IF (@Objeto IS NULL)
            SET @query = @query + '
    WHERE 1=1'
 
        SET @query = @query + '
        AND schema_ori.name = ''' + @Schema + ''''
 
    END
 
 
    
    SET @query = @query + '
    ORDER BY
        schema_ori.name, objeto_dest.name'
 
    
    EXEC(@query)
 
 
END

Now let's take a look at the result:
SQL Server - Foreign Key Foreign key FK

Remembering that when you are going to delete the FK's, copy the scripts BEFORE generating them again. Once the FK's are removed, you will not be able to regenerate these scripts using the SP.

How to Remove FK's that reference a table (composite FK)

With the procedure below, you can easily identify and remove / recreate the FK's of a given table, which have foreign key references for other objects, even if they use a composite key (more than one column forming the FK).

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
CREATE PROCEDURE [dbo].[stpRecria_FK_Composta] (
    @Database [sysname],
    @Objeto [sysname] = NULL,
    @Schema [sysname] = 'dbo'
)
AS
BEGIN
    
    DECLARE @query VARCHAR(MAX) = '
    
;WITH CTE
AS (
    SELECT
        FK.[object_id] AS Id_FK,
        FK.[name] AS Ds_Nome_FK,
        schema_ori.[name] AS Ds_Schema_Origem,
        schema_dest.[name] AS Ds_Schema_Destino,
        objeto_ori.[object_id] AS Id_Objeto_Origem,
        objeto_dest.[object_id] AS Id_Objeto_Destino,
        objeto_ori.[name] AS Ds_Tabela,
        objeto_dest.[name] AS Ds_Tabela_Referencia,
        schema_ori.[name] + ''.'' + objeto_ori.[name] AS Ds_Objeto,
        schema_dest.[name] + ''.'' + objeto_dest.[name] AS Ds_Objeto_Referencia,
 
        STUFF((
            SELECT '', '' + ISNULL(C1.[name], '''')
            FROM [' + @Database + '].sys.foreign_keys A1 WITH(NOLOCK)
            JOIN [' + @Database + '].sys.foreign_key_columns B1 WITH(NOLOCK) ON A1.[object_id] = B1.constraint_object_id
            JOIN [' + @Database + '].sys.columns C1 WITH(NOLOCK) ON B1.parent_object_id = C1.[object_id] AND B1.parent_column_id = C1.column_id
            WHERE ISNULL(C1.[object_id], '''') = ISNULL(objeto_ori.[object_id], '''')
            AND ISNULL(A1.[object_id], '''') = ISNULL(FK.[object_id], '''')
            ORDER BY C1.[name]
            FOR XML PATH('''')), 1, 2, ''''
        ) AS Colunas_Origem,
 
        STUFF((
            SELECT '', '' + ISNULL(C1.[name], '''')
            FROM [' + @Database + '].sys.foreign_keys A1 WITH(NOLOCK)
            JOIN [' + @Database + '].sys.foreign_key_columns B1 WITH(NOLOCK) ON A1.[object_id] = B1.constraint_object_id
            JOIN [' + @Database + '].sys.columns C1 WITH(NOLOCK) ON B1.referenced_object_id = C1.[object_id] AND B1.referenced_column_id = C1.column_id
            WHERE ISNULL(C1.[object_id], '''') = ISNULL(objeto_dest.[object_id], '''')
            AND ISNULL(A1.[object_id], '''') = ISNULL(FK.[object_id], '''')
            ORDER BY C1.[name]
            FOR XML PATH('''')), 1, 2, ''''
        ) AS Colunas_Destino
    FROM
        [' + @Database + '].sys.foreign_keys                    AS FK            WITH(NOLOCK)
        JOIN [' + @Database + '].sys.foreign_key_columns        AS FK_Coluna    WITH(NOLOCK)    ON FK.[object_id] = FK_Coluna.constraint_object_id
    
        JOIN [' + @Database + '].sys.objects                    AS objeto_ori    WITH(NOLOCK)    ON FK.parent_object_id = objeto_ori.[object_id]
        JOIN [' + @Database + '].sys.objects                    AS objeto_dest    WITH(NOLOCK)    ON FK.referenced_object_id = objeto_dest.[object_id]
 
        JOIN [' + @Database + '].sys.schemas                    AS schema_ori    WITH(NOLOCK)    ON objeto_ori.[schema_id] = schema_ori.[schema_id]
        JOIN [' + @Database + '].sys.schemas                    AS schema_dest    WITH(NOLOCK)    ON FK.[schema_id] = schema_dest.[schema_id]
    
        JOIN [' + @Database + '].sys.columns                    AS coluna_ori    WITH(NOLOCK)    ON FK_Coluna.parent_object_id = coluna_ori.[object_id] AND FK_Coluna.parent_column_id = coluna_ori.column_id
    GROUP BY
        FK.[object_id],
        FK.[name],
        objeto_ori.[object_id],
        objeto_dest.[object_id],
        schema_ori.[name],
        schema_dest.[name],
        schema_ori.[name] + ''.'' + objeto_ori.[name],
        schema_dest.[name] + ''.'' + objeto_dest.[name],
        objeto_ori.[name],
        objeto_dest.[name]
)
SELECT
    CTE.Ds_Nome_FK,
    CTE.Ds_Objeto,
    CTE.Ds_Objeto_Referencia,
    CTE.Colunas_Origem,
    CTE.Colunas_Destino,
    ''ALTER TABLE [' + @Database + '].['' + CTE.Ds_Schema_Origem + ''].['' + CTE.Ds_Tabela + ''] DROP CONSTRAINT ['' + CTE.Ds_Nome_FK  + '']'' AS Dropar_FK,
    ''ALTER TABLE [' + @Database + '].['' + CTE.Ds_Schema_Origem + ''].['' + CTE.Ds_Tabela + ''] ADD CONSTRAINT ['' + CTE.Ds_Nome_FK + ''] FOREIGN KEY ('' + CTE.Colunas_Origem + '') REFERENCES [' + @Database + '].['' + CTE.Ds_Schema_Destino + ''].['' + CTE.Ds_Tabela_Referencia + ''] ('' + CTE.Colunas_Destino + '')'' AS Criar_FK
FROM
    CTE'
 
 
    IF (NULLIF(LTRIM(RTRIM(@Objeto)), '') IS NOT NULL)
    BEGIN
 
        SET @query = @query + '
WHERE
    Ds_Tabela_Referencia = ''' + @Objeto + ''''
 
    END
    
 
    IF (NULLIF(LTRIM(RTRIM(@Schema)), '') IS NOT NULL)
    BEGIN
 
        IF (@Objeto IS NULL)
            SET @query = @query + '
WHERE
    1=1'
 
        SET @query = @query + '
    AND Ds_Schema_Origem = ''' + @Schema + ''''
 
    END
 
 
    
    SET @query = @query + '
ORDER BY
    Ds_Objeto_Referencia,
    Ds_Nome_FK'
 
    
    EXEC(@query)
 
 
END

Result of the procedure:

How to Remove FK's from a Table

With the procedure below, you can easily identify and remove / recreate the FK's of a given table, which have foreign key references for other objects.

View source
Transact-SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
CREATE PROCEDURE [dbo].[stpRecria_FK_Tabela]
    @Database [sysname],
    @Objeto [sysname] = NULL,
    @Schema [sysname] = NULL
WITH EXECUTE AS CALLER
AS
BEGIN
    
    --
    -- DROP CONSTRAINTS
    --
 
    DECLARE @Db_Id sysname = (SELECT database_id FROM sys.databases WHERE name = @Database)
    DECLARE @query VARCHAR(MAX), @cmd VARCHAR(MAX)
 
    SET @query = '
    SELECT
        ''ALTER TABLE [' + @Database + '].['' + schema_ori.name + ''].['' + objeto.name + ''] DROP CONSTRAINT ['' + FK.name  + '']'' AS Dropar_FKs
    FROM
        ' + @Database + '.sys.foreign_keys                AS FK
        JOIN ' + @Database + '.sys.foreign_key_columns    AS FK_Coluna ON FK.object_id = FK_Coluna.constraint_object_id
        JOIN ' + @Database + '.sys.objects                AS objeto ON FK.parent_object_id = objeto.object_id
        JOIN ' + @Database + '.sys.schemas                AS schema_ori ON objeto.schema_id = schema_ori.schema_id'
 
 
    IF (@Objeto IS NOT NULL)
    BEGIN
 
        SET @query = @query + '
    WHERE
        objeto.name = ''' + @Objeto + ''''
 
    END
 
 
    IF (@Schema IS NOT NULL)
    BEGIN
 
        IF (@Objeto IS NULL)
            SET @query = @query + '
    WHERE 1=1'
 
        SET @query = @query + '
        AND schema_ori.name = ''' + @Schema + ''''
 
    END
 
 
    EXEC(@query)
 
 
 
    --
    -- RECREATE CONSTRAINTS
    --
 
    SET @query = '
    SELECT
        ''ALTER TABLE [' + @Database + '].['' + schema_ori.name + ''].['' + objeto.name + ''] '' +
        ''ADD CONSTRAINT ['' + fk.name + ''] FOREIGN KEY ('' + colunas.name + '') '' +
        ''REFERENCES ['' + schema_ref.name + ''].['' + objeto_ref.name + ''] ('' + colunas_ref.name + '')'' as Recriar_FKs
    FROM
        ' + @Database + '.sys.foreign_keys AS fk
        JOIN ' + @Database + '.sys.foreign_key_columns AS fc ON fk.object_id = fc.constraint_object_id
 
        JOIN ' + @Database + '.sys.objects objeto ON fk.parent_object_id = objeto.object_id
        JOIN ' + @Database + '.sys.columns colunas ON fc.parent_column_id = colunas.column_id AND fk.parent_object_id = colunas.object_id
        JOIN ' + @Database + '.sys.schemas schema_ori ON objeto.schema_id = schema_ori.schema_id
 
        JOIN ' + @Database + '.sys.objects objeto_ref ON fc.referenced_object_id = objeto_ref.object_id
        JOIN ' + @Database + '.sys.columns colunas_ref ON fc.referenced_column_id = colunas_ref.column_id AND fk.referenced_object_id = colunas_ref.object_id
        JOIN ' + @Database + '.sys.schemas schema_ref ON objeto_ref.schema_id = schema_ref.schema_id'
    
 
    IF (@Objeto IS NOT NULL)
    BEGIN
 
        SET @query = @query + '
    WHERE
        objeto.name = ''' + @Objeto + ''''
 
    END
 
 
    IF (@Schema IS NOT NULL)
    BEGIN
 
        IF (@Objeto IS NULL)
            SET @query = @query + '
    WHERE 1=1'
 
        SET @query = @query + '
        AND schema_ori.name = ''' + @Schema + ''''
 
    END
 
 
    EXEC(@query)
 
 
END

Result of the procedure:
SQL Server - Rebuild FK from 2 Table

That's it folks!
To the next!

sql, sql server, transact sql, tsql, fk, foreign key, foreign key, error, how to identify, how to remove, how to recreate, query, procedure

tags: fkforeign keysqlsql servertsql

You may also like ...

  • CLR - Solving the System.Security.Permissions.FileIOPermission problem in SQL Server

  • SQL Server Express and Developer - How far can we use free versions of SQL Server?

  • 2 Chapter SQL Server ES Meeting - 10 / 06 / 2017

  • Next Understanding Index Functioning in SQL Server
  • Previous Enabling and using dedicated remote administrator connection (DAC) in SQL Server

Comments

  • Comments3
  • Pingbacks0
  1. neemias fonseca grandson said:
    10 May 2019 to 11: 55

    FK script composed with problem executing that does not return query. Solved by editing and placing select to set to @query and not @cmd.

    Reply
    • Dirceu Resende said:
      11 May 2019 to 20: 42

      Thanks for the tip Nehemiah, I already made the correction in the script. Thanks 🙂

      Reply
  2. Legionary said:
    December 5 from 2016 to 16: 36

    Very useful script especially for those who have to load data on a daily basis. Very grateful for this excellent post.

    Reply

Leave a Comment Cancel reply

Dirceu Resende © 2020. All Rights Reserved.