sql-server - Delete sql rows with fk in another table

Question

I have an example uni database where I am required to update department for the new year. I have the following tables

Table 1 (Department) has the following columns:

ID   | NAME | SCHOOL | ACTIVE | SNAPSHOT
860  | SOF  |  20    |   1    | NULL
861  | CS   |  20    |   1    | NULL

ID is PK, int, NOT NULL

Table 2 (User) has the following columns:

ID   | EMPNO | FORENAME | SURNAME | DEPT
1495 | e3456 | Parker   | Sal     |  860

Dept is a FK

Problem:

The two departments in Table 1 should be updated to a single department as below:

ID   | NAME                | SCHOOL | ACTIVE | SNAPSHOT
860  | School of Sciences  |  20    |   1    | NULL

I am using the following sql command :

DELETE FROM Department WHERE ID = 861

And receiving the following error:

Msg 547, Level 16, State 0, Line 10 The DELETE statement conflicted with the REFERENCE constraint "FK__User__Dept". The conflict occurred in database "uniDB", table "dbo.User", column 'Dept'.

Using the same command I am unable to delete the Dept ID 861 from table 2 (User) since a third table has FK constraint User and therefore, I get the same error as above.

Thanks for your help in advance

标签： sql-servertsqlforeign-keys

Answer 1

更改链接，然后删除：

UPDATE u SET
  Dept = 860
FROM User u
WHERE u.Dept = 861

DELETE d
FROM Department d
WHERE d.ID = 861