Akhilesh Kumar Akhilesh Kumar - 11 months ago 69
MySQL Question

Drop a foreign constraint between columns of two table without knowing Foreign key name in INFORMIX

I'm using an Informix database. I have added some foreign keys to a table without giving them a name e.g.

ALTER TABLE myreport
ADD CONSTRAINT FOREIGN KEY (rid) REFERENCES report(id),
ADD CONSTRAINT FOREIGN KEY (uid) REFERENCES user(id);


Now I want to delete the foreign key between
uid
and
id
of
user
table.

I can get name of the constraints on the table using the following:

SELECT constrname
FROM sysconstraints
WHERE tabid = (SELECT tabid FROM systables WHERE tabname = 'myreport');


which gives following output:

constrname u169_201
constrname n169_202
constrname n169_203
constrname n169_204
constrname n169_205
constrname n169_206
constrname n169_207
constrname r169_276
constrname r169_277


I don't know which constraints belongs to foreign key between
uid
and
id
of
user
table.

Answer Source

I tried following it worked:

CREATE PROCEDURE INFORMIX.REMOVE_FK()
    DEFINE tmpvr varchar(250);
    FOREACH cur1 FOR select  b.constrname into tmpvr
            from systables a, sysconstraints b, sysindexes c, syscolumns d
            WHERE a.tabname = 'myreport' AND (d.colname='uid' or d.colname='rid')
            AND b.tabid = a.tabid AND c.idxname = b.idxname AND d.tabid = a.tabid AND (
                d.colno = c.part1 or d.colno = c.part2 or d.colno = c.part3 or d.colno = c.part4 or
                d.colno = c.part5 or d.colno = c.part6 or d.colno = c.part7 or d.colno = c.part8 or
                d.colno = c.part9 or d.colno = c.part10 or d.colno = c.part11 or d.colno = c.part12 or
                d.colno = c.part13 or d.colno = c.part14 or d.colno = c.part15 or d.colno = c.part16
            )
    EXECUTE IMMEDIATE 'ALTER TABLE myreport DROP CONSTRAINT (' || tmpvr || ')';
    END FOREACH
END PROCEDURE;
EXECUTE PROCEDURE INFORMIX.REMOVE_FK();
DROP PROCEDURE INFORMIX.REMOVE_FK();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download