All geek questions in one place

SQL Server: how to ignore referential integrity before COMMIT?

I have a process of moving rows from one database to another. Due to some circular chains of foreign key references, I cannot delete rows from the old database and not insert them into the new database.

Since the entire operation is performed in transaction 1, I want SQL Server to ignore referential integrity failures until I call COMMIT TRANSACTION.

For example, 2 :

   Table: Turboencabulators         Table: Marselvanes
   =========================        =======================
PK TurboencabulatorID int    /-> PK MarselvaneID       int
^  MarselvanesID      int --/       HasGrammeter       bit
|                                   PantametricFan     varchar(50)
+-------------------------------    TurboencabulatorID int

If I try to insert a turbocharger in a new table, it will fail if there is no more marselvane. Reverse ordering has the same problem.

When I try to delete old lines, I cannot delete one until the other is deleted.

n - , , , null. , . , , , FK, . 3

, , - T-SQL, SQL Server , commit.

1
2
3

+3
2

...

ALTER TABLE whatever_table NOCHECK CONSTRAINT ALL

, ...

ALTER TABLE whatever_table CHECK CONSTRAINT ALL

.

-don

+10

, .

, lookups/checks/cascade, //. , FK: "" FK. , - , . " , ", , , . , , ? , , , corrcetive : , . , , - , , . , .

, , - , . A FK B. , B, A, A, B, . FK , FK . , , !

, , , cascadin hierachy . CS- , . , . , NULL post insert.

, - no-no: , , . "" , ( , ).

+2

Source: https://habr.com/ru/post/1734618/

More articles:


All Articles