ALTER TABLE< TABLE> DROP UNIQUE< INDNAME>无法识别索引名称！

本文介绍了ALTER TABLE&lt; TABLE&gt; DROP UNIQUE&lt; INDNAME&gt;无法识别索引名称！的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述 限时删除！！ 平台是DB2 / NT 7.2.9 表格是这样创建的： CREATE TABLE MYTEST（ MYTESTOID bigint not null主键， FK_OTHEROID bigint not null引用其他， FK_ANOTHEROID bigint not null引用另一个， FK_LASTLYOID bigint not null引用最后， 唯一（FK_OTHEROID，FK_ANOTHEROID）） 所以我现在在SYSCAT.INDEXES中显示两个自动命名的索引： INDSCHEMA INDNAME DEFINER TABSCHEMA TABNAME COLNAMES UNIQUERULE SYSIBM SQL040102143652040管理员MYSCHEMA MYTEST + MYTESTOID P SYSIBM SQL040102143652150管理员MYSCHEMA MYTEST + FK_OTHEROID + FK_ANOTHEROID几个月后，我们发现OOPS，FK_LASTLYOID也应该已经包含在这个独特的约束中了！ 好​​了，所以我们无法修改索引，我们必须将ALTER TABLE删除它，然后再添加一个新的。我很好。 ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150 错误：COM.ibm.db2.jdbc.DB2Exception：[IBM ] [CLI驱动程序] [DB2 / NT] SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704 嗯。也许它不喜欢我当前的架构？ ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SYSIBM.SQL040102143652150 错误：COM.ibm.db2.jdbc .DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT] SQL0108N名称SQL040102143652150有错误数量的 资格赛。 SQLSTATE = 42601 好​​的，不能限定架构，我会将当前架构设置为SYSIBM。 SET CURRENT SCHEMA SYSIBM ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150 错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI Driver] [DB2 / NT] SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704 SET CURRENT SCHEMA MYSCHEMA ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150 错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT] SQL0204NSQL040102143652150是一个未定义的名称。 SQLSTATE = 42704 没有区别。我猜它应该能够推断出指数 架构。 也许它被视为约束？ ALTER TABLE MYSCHEMA.MYTEST DROP CONSTRAINT SQL040102143652150 错误：COM.ibm.db2.jdbc.DB2Exception：[IBM] [CLI驱动程序] [DB2 / NT] SQL0204N" SQL040102143652150"是一个未定义的名称。 SQLSTATE = 42704 大鼠。我究竟做错了什么？我很确定这是正确的索引 名称。 我不是数据库管理员，所以请给出SQL示例！如果唯一的答案是 是删除表格，修改索引，然后重新加载 （而且我真的希望 它不是），告诉我如何做到这一点！ :-)我只是一个愚蠢的程序员... 谢谢！Platform is DB2/NT 7.2.9The table was created like this:CREATE TABLE MYTEST (MYTESTOID bigint not null primary key,FK_OTHEROID bigint not null references other,FK_ANOTHEROID bigint not null references another,FK_LASTLYOID bigint not null references lastly,unique (FK_OTHEROID,FK_ANOTHEROID))So I now have two auto-named indexes showing in SYSCAT.INDEXES:INDSCHEMAINDNAMEDEFINERTABSCHEMATABNAMECOLNAMESUNIQUERULESYSIBM SQL040102143652040ADMINISTRATORMYSCHEMAMYTEST+MYTESTOIDPSYSIBMSQL040102143652150ADMINISTRATORMYSCHEMAMYTEST+FK_OTHEROID+FK_ANOTHEROIDUA few months later, we find out that, OOPS, FK_LASTLYOID should ALSOhave been included in the unique constraint!OK, so we cannot modify the index, we must ALTER TABLE to drop it, thenadd a new one. I''m fine with that.ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704Hmmm. Maybe it doesn''t like my current schema?ALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SYSIBM.SQL040102143652150Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]SQL0108N The name "SQL040102143652150" has the wrong number ofqualifiers. SQLSTATE=42601OK, can''t qualify the schema, I''ll set current schema to SYSIBM.SET CURRENT SCHEMA SYSIBMALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704Try setting current schema to the table schema?SET CURRENT SCHEMA MYSCHEMAALTER TABLE MYSCHEMA.MYTEST DROP UNIQUE SQL040102143652150Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704No difference. I guess it is supposed to be able to infer the indexschema.Maybe it is treated as a Constraint?ALTER TABLE MYSCHEMA.MYTEST DROP CONSTRAINT SQL040102143652150Error: COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver][DB2/NT]SQL0204N "SQL040102143652150" is an undefined name. SQLSTATE=42704Rats. What am I doing wrong? I''m pretty sure that is the correct indexname.I am NOT a DB admin, so please, give SQL examples! If the only answeris "Drop the table, modify the index, and reload it" (and I REALLY hopeit isn''t), show me HOW to do that! :-) I''m just a dumb programmer...Thanks!推荐答案 ''alter table mytest drop unique< indexname>''应该有效，它确实是 从命令行工作。也许这是java驱动程序中的一个错误？ 也许安装最新的fixpack会有所帮助。 我更喜欢定义命名约束，比如 ''约束FK_CONSTRAINT唯一（FK_OTHEROID，FK_ANOTHEROID）'' 你可以放弃它而没有问题 ''alter table mytest drop constraint FK_CONSTRAINT'' 所以你不必知道系统生成了哪个名字。''alter table mytest drop unique <indexname>'' should work, it doeswork from the command line. Maybe it''s a bug in the java driver?Maybe installing the latest fixpack will help.I prefer to define a named constraint, something like''constraint FK_CONSTRAINT unique (FK_OTHEROID,FK_ANOTHEROID)''You can drop it without a problem with''alter table mytest drop constraint FK_CONSTRAINT''so you don''t have to find out which name was generated by the system. 你需要找出唯一约束的名称（这是可能 与索引名称不同）。然后ALTER TABLE DROP约束。 我的猜测是DB2也将丢弃唯一索引。如果没有，你可以使用 DROP INDEX摆脱它。 干杯 Serge - Serge Rielau DB2 SQL编译器开发 IBM多伦多实验室You need to find out the name of the unique constraint (which is likelydifferent than the index name). Then ALTER TABLE DROP the constraint.My guess is that DB2 will also drop the unique index. If not you can useDROP INDEX to get rid of it.CheersSerge--Serge RielauDB2 SQL Compiler DevelopmentIBM Toronto Lab 这篇关于ALTER TABLE&lt; TABLE&gt; DROP UNIQUE&lt; INDNAME&gt;无法识别索引名称！的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 1403页，肝出来的..