Devchat/fix_constraints.sql at main · Adam-Etyang/Devchat · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
-- Fix the foreign key constraint issue
-- This script should be run manually on your PostgreSQL database

-- First, let's see what constraints exist
SELECT
    TC.CONSTRAINT_NAME,
    TC.TABLE_NAME,
    KCU.COLUMN_NAME,
    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
    TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND TC.TABLE_NAME = 'issues';

-- Drop the problematic constraint that references app-users (with hyphen)
DO $$

BEGIN

    -- Drop the constraint that references app-users
    IF EXISTS (
        SELECT
            1
        FROM
            INFORMATION_SCHEMA.TABLE_CONSTRAINTS
        WHERE
            CONSTRAINT_NAME = 'fki5cm6wmfqgvyklpqcbvw1hs54'
    ) THEN
        ALTER TABLE ISSUES DROP CONSTRAINT FKI5CM6WMFQGVYKLPQCBVW1HS54;
        RAISE NOTICE 'Dropped constraint fki5cm6wmfqgvyklpqcbvw1hs54';
    ELSE
        RAISE NOTICE 'Constraint fki5cm6wmfqgvyklpqcbvw1hs54 does not exist';
    END IF;
END $$;


-- Add the correct foreign key constraint that references app_users table (with underscore)
ALTER TABLE ISSUES
    ADD CONSTRAINT FK_ISSUES_REPORTER_CORRECT FOREIGN KEY (
        REPORTER_ID
    )
        REFERENCES APP_USERS(
            ID
        );

        -- Verify the constraint was added correctly
        SELECT TC.CONSTRAINT_NAME, TC.TABLE_NAME, KCU.COLUMN_NAME, 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 TC.CONSTRAINT_TYPE = 'FOREIGN KEY' AND TC.TABLE_NAME = 'issues' AND TC.CONSTRAINT_NAME = 'fk_issues_reporter_correct';

        -- Verify the constraint was added correctly
        SELECT TC.CONSTRAINT_NAME, TC.TABLE_NAME, KCU.COLUMN_NAME, 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 TC.CONSTRAINT_TYPE = 'FOREIGN KEY' AND TC.TABLE_NAME = 'issues' AND TC.CONSTRAINT_NAME = 'fk_issues_reporter_correct';

        -- Verify the constraint was added correctly
SELECT
    TC.CONSTRAINT_NAME,
    TC.TABLE_NAME,
    KCU.COLUMN_NAME,
    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
    TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND TC.TABLE_NAME = 'issues'
    AND TC.CONSTRAINT_NAME = 'fk_issues_reporter_correct';

-- Verify the constraint was added correctly
SELECT
    TC.CONSTRAINT_NAME,
    TC.TABLE_NAME,
    KCU.COLUMN_NAME,
    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
    TC.CONSTRAINT_TYPE = 'FOREIGN KEY'
    AND TC.TABLE_NAME = 'issues'
    AND TC.CONSTRAINT_NAME = 'fk_issues_reporter_correct';