TSQL load db with bunch of FKs, algorithm

Question

I need to load 50+ tables from scratch which have bunch of FK constraints, for now I just drop all FKs, load tables in any order (my data is compliant with all FKs) , then ADD FKs. What is other way to do this, order for FK definition is critical as I can see, so let say looking at my sample, is this the right way to do ?

 ALTER TABLE CUST  ADD CONSTRAINT CUS_BatchID_FK FOREIGN KEY (BatchID) REFERENCES Batch(BatchID);
ALTER TABLE CYCLE ADD CONSTRAINT CL_BatchID_FK  FOREIGN KEY (BatchID) REFERENCES Batch(BatchID);
ALTER TABLE QOUTE ADD CONSTRAINT QT_BatchID_FK  FOREIGN KEY (BatchID) REFERENCES Batch(BatchID);

ALTER TABLE MD_LOC ADD CONSTRAINT MLOC_FK       FOREIGN KEY (LOC_ID) REFERENCES LOC(LOC_ID);
ALTER TABLE CUST   ADD CONSTRAINT CUST_PROV_FK  FOREIGN KEY (PROV_ID) REFERENCES PROVIDER(PROV_ID);   
ALTER TABLE REFER ADD CONSTRAINT  RF_CUST_ID_FK FOREIGN KEY (CUST_ID) REFERENCES CUST(CUST_ID); 

/*--------------------
1. load  Batch
2. load  LOC
2a. Load  MD_LOC
3.  load  PROVIDER 
3a. load CUST 
.....

I.e I'm loaded tables first without FK, then go in sequence, I tested it for couple of tables and it works, just want to confirm if anything might be missing before I go with the rest of tables.

Possible duplicate of [How can foreign key constraints be temporarily disabled using T-SQL?](https://stackoverflow.com/questions/159038/how-can-foreign-key-constraints-be-temporarily-disabled-using-t-sql) — HABO, Nov 29 '17 at 03:35
Tip: It's helpful to tag database questions with both the appropriate software (MySQL, Oracle, DB2, ...) and version, e.g. `sql-server-2014`. Differences in syntax and features often affect the answers. Note that `tsql` narrows the choices, but does not specify the database. — HABO, Nov 29 '17 at 03:36

score 3 · Accepted Answer · answered Nov 29 '17 at 02:50

Figuring out the order of table inserts can be achieved by thinking of the foreign keys like a parent/child relationships and in T-SQL we can use recursive common table expressions for such hierarchies. The following will list the order of table inserts that will honour the FK constraints:

WITH
      ctefk (pktable, fktable) AS (
                  SELECT
                        s1.name + '.' + o1.name AS pktable
                      , ISNULL(s2.name + '.' + o2.name, '') as fktable
                  FROM sys.objects o1
                  LEFT OUTER JOIN sys.sysforeignkeys fk ON o1.object_id = fk.fkeyid
                  LEFT OUTER JOIN sys.objects o2 ON o2.object_id = fk.rkeyid
                  LEFT OUTER JOIN sys.schemas s1 ON o1.schema_id = s1.schema_id
                  LEFT OUTER JOIN sys.schemas s2 ON o2.schema_id = s2.schema_id
                  WHERE o1.type_desc = 'user_table'
                  AND o1.name NOT IN ('dtproperties', 'sysdiagrams')
                  GROUP BY
                        s1.name + '.' + o1.name
                      , ISNULL(s2.name + '.' + o2.name, '')
            ),
      cterec (tablename, fkcount) AS (
                  SELECT
                        tablename = pktable
                      , fkcount =   0
                  FROM ctefk

                  UNION ALL

                  SELECT
                        tablename = pktable
                      , fkcount =   1
                  FROM ctefk
                  CROSS APPLY cteRec
                  WHERE ctefk.fktable = cteRec.tablename
                  AND ctefk.pktable <> cteRec.tablename
            )
SELECT
      TableName
    , insertorder = DENSE_RANK() OVER (ORDER BY MAX(fkcount) ASC)
FROM (
      SELECT
            tablename = fktable
          , fkcount =   0
      FROM ctefk
      GROUP BY
            fktable

      UNION ALL

      SELECT
            tablename = tablename
          , fkcount =   SUM(ISNULL(fkcount, 0))
      FROM cterec
      GROUP BY
            tablename
) x
WHERE x.tablename <> ''
GROUP BY
      tablename
ORDER BY
      insertorder ASC
    , TableName ASC
;

NB: Any table with a FK to itself will (obviously) still be a problem, hopefully this does not occur at all. The query above skips over such a condition as it causes a loop in the recursion.

A pleasure but the kudos goes to the source... I started writing one, then stumbled over that and decided it was better then mine :) — Paul Maxwell, Nov 29 '17 at 23:53

TSQL load db with bunch of FKs, algorithm

1 Answers1