Azure-docs: DROP/CREATE Constraint script in #4 is broken

[AgSync-Aaron]

DO NOT USE THE SCRIPT IN STEP 4

Do not use the script as it is written in step 4. I provided my feedback during the private preview and the script was not fixed then. Maybe it will be fixed now. 🤞🏻

The documented script does not include schemas in the query. If you have the same table name in multiple schemas the script will group them together.

SELECT Q.table_name
    ,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(CONCAT(' DROP CONSTRAINT ', foreignkey), ','), ';') as DropQuery
    ,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(CONCAT(' ADD CONSTRAINT ', foreignkey, ' FOREIGN KEY (', column_name, ')', ' REFERENCES ', foreign_table_schema, '.', foreign_table_name, '(', foreign_column_name, ')' ), ','), ';') as AddQuery
FROM
    (SELECT DISTINCT
        tc.table_schema,
        tc.constraint_name AS foreignkey,
        tc.table_name,
        kcu.column_name,
        ccu.table_schema AS foreign_table_schema,
        ccu.table_name AS foreign_table_name,
        ccu.column_name AS foreign_column_name
    FROM information_schema.table_constraints AS tc
    JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
    JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
    WHERE constraint_type = 'FOREIGN KEY') Q
GROUP BY Q.table_schema, Q.table_name;

---

Document Details

⚠ Do not edit this section. It is required for docs.microsoft.com ➟ GitHub issue linking.

• ID: 261bad06-6551-260a-cbae-9f17f7e84246
• Version Independent ID: 3e542277-4062-97dd-5039-b460d1e7f140
• Content: Tutorial: Migrate PostgreSQL to Azure DB for PostgreSQL online via the Azure portal - Azure Database Migration Service
• Content Source: articles/dms/tutorial-postgresql-azure-postgresql-online-portal.md
• Service: dms
• GitHub Login: @HJToland3
• Microsoft Alias: jtoland

[AgSync-Aaron]

Also in step 6 schemas are ignored as well.

SELECT DISTINCT CONCAT('ALTER TABLE ', event_object_schema, '.', event_object_table, ' DISABLE TRIGGER ', trigger_name, ';')
FROM information_schema.triggers

[BryanTrach-MSFT]

@AgSync-Aaron Thanks for the feedback! We are currently investigating and will update you shortly.

[AgSync-Aaron]

I found further issues with the script. If there are multiple columns in the constraint, the script totally breaks. I don't have a complete fix for that yet.

[HJToland3]

@AgSync-Aaron, I have a note out to the PMs on this and will respond as soon as possible.

[HJToland3]

@AgSync-Aaron, we've rewritten the query to support schemas and individual columns within a constraint:

SELECT Q.table_name

,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(DISTINCT CONCAT(' DROP CONSTRAINT ', foreignkey), ','), ';') as DropQuery
,CONCAT('ALTER TABLE ', table_schema, '.', table_name, STRING_AGG(DISTINCT CONCAT(' ADD CONSTRAINT ', foreignkey, ' FOREIGN KEY (', column_name, ')', ' REFERENCES ', foreign_table_schema, '.', foreign_table_name, '(', foreign_column_name, ')' ), ','), ';') as AddQuery

FROM
(SELECT
S.table_schema,
S.foreignkey,
S.table_name,
STRING_AGG(DISTINCT S.column_name, ',') AS column_name,
S.foreign_table_schema,
S.foreign_table_name,
STRING_AGG(DISTINCT S.foreign_column_name, ',') AS foreign_column_name
FROM
(
SELECT DISTINCT
tc.table_schema,
tc.constraint_name AS foreignkey,
tc.table_name,
kcu.column_name,
ccu.table_schema AS foreign_table_schema,
ccu.table_name AS foreign_table_name,
ccu.column_name AS foreign_column_name
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema
WHERE constraint_type = 'FOREIGN KEY'
) S
GROUP BY S.table_schema, S.foreignkey, S.table_name, S.foreign_table_schema, S.foreign_table_name
) Q
GROUP BY Q.table_schema, Q.table_name;

The query you wrote for step 6 should be fine.

[AgSync-Aaron]

I will try it out. Thanks. I didn't see your message in time and wrote my own using the postgres views and functions.

SELECT s.nspname AS schema, t.relname AS table_name,
  CONCAT('ALTER TABLE "', s.nspname, '"."', t.relname, '"', STRING_AGG(CONCAT(' DROP CONSTRAINT "', c.conname, '" '), ',' ORDER BY c.conname)) AS drop_constraint,
  CONCAT('ALTER TABLE "', s.nspname, '"."', t.relname, '"', STRING_AGG(CONCAT(' ADD CONSTRAINT "', c.conname, '" ', pg_get_constraintdef(c.oid)), ',' ORDER BY c.conname)) AS add_constraint
FROM pg_constraint c
JOIN pg_class t ON c.conrelid = t.oid
JOIN pg_namespace s ON t.relnamespace = s.oid
WHERE c.contype IN ('f')
GROUP BY s.nspname, t.relname
ORDER BY s.nspname, t.relname

[Mike-Ubezzi-MSFT]

@AgSync-Aaron We will now proceed to close this thread. If there are further questions regarding this matter, please comment and we will gladly continue the discussion.