在不知道Informix中外键名称的情况下,在两个表的列之间删除外键约束? [英] Drop a foreign key constraint between columns of two tables without knowing the foreign key name in Informix?
本文介绍了在不知道Informix中外键名称的情况下,在两个表的列之间删除外键约束?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我正在使用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);
现在,我要删除user
表的uid
和id
之间的外键.
Now I want to delete the foreign key between uid
and id
of user
table.
我可以使用以下方法获取表上约束的名称:
I can get the name of the constraints on the table using the following:
SELECT constrname
FROM sysconstraints
WHERE tabid = (SELECT tabid FROM systables WHERE tabname = 'myreport');
给出以下输出:
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
我不知道哪个约束属于user
表的uid
和id
之间的外键.
I don't know which constraint belongs to the foreign key between uid
and id
of user
table.
推荐答案
我尝试了以下操作:
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();
这篇关于在不知道Informix中外键名称的情况下,在两个表的列之间删除外键约束?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文