Конфликт инструкции update с ограничением reference

1
2
3
4

USE project;
UPDATE Паспорт
    SET Номер_катера=456
        WHERE Номер_катера=123;

1
2
3
4
5
6
7
8
9
10

USE project;
CREATE TABLE Рейсы (Номер_рейса INT IDENTITY (1,1),
                    Номер_катера INTEGER NOT NULL,
                    Дата_выхода DATE NOT NULL,
                    Время_выхода TIME NOT NULL,
                    Дата_возвращения DATE NOT NULL,
                    Время_возвращения TIME NOT NULL,
            CONSTRAINT pk2_id PRIMARY KEY (Номер_рейса),
            CONSTRAINT fk6 FOREIGN KEY (Номер_катера) REFERENCES Паспорт ON UPDATE CASCADE
            );

1
2
3
4
5
6
7
8
9
10

USE project;
CREATE TABLE Команда (ID INT IDENTITY (1,1),
                        Имя_рыбака CHAR (20) NOT NULL,
                        Номер_должности INT NOT NULL,
                        Адрес CHAR (30) NOT NULL,
                        Номер_катера INT NOT NULL,
                CONSTRAINT pk3_id PRIMARY KEY (ID),
                CONSTRAINT fk7 FOREIGN KEY (Номер_катера) REFERENCES Паспорт ON UPDATE CASCADE,
                CONSTRAINT fk8 FOREIGN KEY (Номер_должности) REFERENCES Должность ON  UPDATE CASCADE,
                );

Наверх

Scenario:

You are working as SQL Server developer, You wrote an update statement for one of the table and getting below error.

Msg 547, Level 16, State 0, Line 32
The UPDATE statement conflicted with the REFERENCE constraint «FK_». 
The conflict occurred in database «YourDatabaseName», table «SchemaName.YourTableName», column ‘ColumnName’.
The statement has been terminated.

How to resolve this issue?

Solution:

Let’s create this error first by using below script. We are going to create two tables dbo.Customer and dbo.Orders. The tables has Primary-Foreign Key Relationship.

USE YourDatabaseName
GO

CREATE TABLE dbo.Customer (
    Customerid INT PRIMARY KEY
    ,FName VARCHAR(100)
    ,LName VARCHAR(100)
    ,SSN VARCHAR(10)
    )


    CREATE TABLE dbo.Orders (
    OrderId INT Identity(1, 1)
    ,OrderitemName VARCHAR(50)
    ,OrderItemAmt INT
    ,Customer_id INT FOREIGN KEY REFERENCES Customer(CustomerId)
    )


    --insert sample data
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (1,'Aamir','Shahzad','000-000-00')

    insert into dbo.Orders
    (OrderItemName,OrderItemAmt,Customer_Id)
    values ('TV',1,1)

Картинка с сайта: www.techbrothersit.com

How to update record when Column is referenced by Foreign Key Constraint in SQL Server

Now let’s say if you feel that CustomerId value is incorrect in dbo.Customer and need to be updated. You wrote below update statement to update CustomerId to 100.

    update dbo.Customer
    set Customerid=100

As there is no Customer_id value=100 in dbo.Orders table, You can’t update the record in reference table. Now you thought that let’s fix the Parent table first ( dbo.Orders) and then I can update the dbo.Customer table.

    update dbo.Orders
    set Customer_Id=100

Again you got the error as shown below, because we don’t have CustomerId=100 available in dbo.Customer table.

From here we can come with with multiple solutions

1) Instead of updating the record, Insert the record in Reference Table ( Dbo.Customer), Then update the record in Parent table (Dbo.Orders) and finally delete the existing records from Reference Table.

    --Insert Record in Reference Table First
     insert into dbo.Customer 
    (CustomerId,FName, LName,SSN)
     values
    (100,'Aamir','Shahzad','000-000-00')

    --Update the Records in Parent Table 
        update dbo.Orders
    set Customer_Id=100

    --Delete the old record from Reference Table
    Delete from dbo.Customer
    where CustomerId=1

Check the records in table now.

Картинка с сайта: www.techbrothersit.com

How to update Column Value when referenced by Foreign Key Constraint in SQL Server

2) Disable the Foreign Key Constraint and Update the Values Manually

Another solution can be, disable the Foreign Key constraint, update the records and finally enable the Foreign key again.

--Find the Foreign Key Constraint with Table Name
    USE YourDatabaseName
    GO
    Select 
    Schema_name(Schema_id) as SchemaName,
    object_name(Parent_object_id) as TableName,
    name as ForeignKeyConstraintName
    from sys.foreign_keys

Disable the Foreign Key Constraint by using below statement

Syntax

I used below statement to disable Foreign Key constraint on dbo.Orders table.

--Disable Foregin Key by using NOCHECK
ALTER TABLE dbo.Orders
NOCHECK CONSTRAINT FK__Orders__Customer__2A4B4B5E

--Run Update Statements
    update dbo.Customer
    set Customerid=100

    update dbo.Orders
    set Customer_Id=100

Enable Foreign Key Constraint Syntax

I execute below script to Enable Foreign Key Constraint on dbo.Orders table.

--Enable Foreign Key Constraint by using CHECK
ALTER TABLE dbo.Orders
CHECK CONSTRAINT FK__Orders__Customer__2A4B4B5E

Just replied on MSDN forum’s following post: http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/289c4ca5-6ab2-4ed6-b446-0300f5c7dc88

When tables are referenced by Foreign Key constraints it gets difficult to UPDATE the key columns.
An example shows this issue and how one can perform UPDATE on FK columns.

USE [tempdb]
GO

-- CREATE test tables
CREATE TABLE dept (d_id INT PRIMARY KEY, dept VARCHAR(50))

CREATE TABLE emp (sn INT PRIMARY KEY, ename VARCHAR(50),
	d_fk INT CONSTRAINT fk_dept_d_fk FOREIGN KEY (d_fk) REFERENCES dept(d_id))

-- INSERT test data
INSERT INTO dept
SELECT 1, 'finance' UNION
SELECT 2, 'hr'

INSERT INTO emp
SELECT 1, 'Manoj', 1 UNION
SELECT 2, 'Saurabh', 2 UNION
SELECT 3, 'Kanchan', 1 UNION
SELECT 4, 'Pooja', 2

-- SELECT the INSERTed data
SELECT * from dept

d_id	dept
1	finance
2	hr	-- DeptID needs to be updated to 3

SELECT * from emp

sn	ename	d_fk
1	Manoj	1
2	Saurabh	2	-- DeptID should also be updated here to 3
3	Kanchan	1
4	Pooja	2	-- DeptID should also be updated here to 3

-- Now lets UPDATE the FK column values
UPDATE dept SET d_id=3 WHERE d_id=2

Error Message:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the REFERENCE constraint "fk_dept_d_fk".
The conflict occurred in database "tempdb", table "dbo.emp", column 'd_fk'.
The statement has been terminated.

UPDATE emp SET d_fk=3 WHERE d_fk=2

Error Message:
Msg 547, Level 16, State 0, Line 1
The UPDATE statement conflicted with the FOREIGN KEY constraint "fk_dept_d_fk".
The conflict occurred in database "tempdb", table "dbo.dept", column 'd_id'.
The statement has been terminated.

We can disable the FK constraints by ALTER statement before the UPDATE and enable them after the UPDATE, like:

-- Disable FK Constraint
ALTER TABLE emp NOCHECK CONSTRAINT fk_dept_d_fk

-- Perform UPDATE
UPDATE dept SET d_id=3 WHERE d_id=2
UPDATE emp SET d_fk=3 WHERE d_fk=2

-- Enable FK Constraint
ALTER TABLE emp WITH CHECK CHECK CONSTRAINT fk_dept_d_fk

-- Following SELECTS will show the new values in updated records:
SELECT * from dept

d_id	dept
1	finance
3	hr	-- Updated record

SELECT * from emp

sn	ename	d_fk
1	Manoj	1
2	Saurabh	3	-- Updated record
3	Kanchan	1
4	Pooja	3	-- Updated record

-- Final Cleanup, DROP the tables.
DROP TABLE emp
DROP TABLE dept

MSDN BOL: http://msdn.microsoft.com/en-us/library/10cetyt6(v=VS.80).aspx