Thanks for the responses. Let me elaborate. Here's my table structure: CREATE TABLE T1 ( id VARCHAR(50) NOT NULL, primary key (id) );
CREATE TABLE T2 ( id VARCHAR(50) NOT NULL, primary key (id) ); CREATE TABLE T3 ( id VARCHAR(50) NOT NULL, t1_id VARCHAR(50) NOT NULL, f1 INTEGER NOT NULL, f2 INTEGER NOT NULL, t2_id1 VARCHAR(50) NOT NULL, t2_id2 VARCHAR(50) NOT NULL, create_time TIMESTAMP NOT NULL, update_time TIMESTAMP, PRIMARY KEY (id), CONSTRAINT t3_uk UNIQUE (t1_id, f1), CONSTRAINT t1_fk FOREIGN KEY (t1_id) REFERENCES t1 (id) ON DELETE RESTRICT, CONSTRAINT t2_fk1 FOREIGN KEY (t2_id1) REFERENCES t2 (id) ON DELETE RESTRICT, CONSTRAINT t2_fk2 FOREIGN KEY (t2_id2) REFERENCES t2 (id) ON DELETE RESTRICT ); When I run this statement: alter table t3 drop constraint t3_uk; the unique index generated for the unique constraint is *NOT *dropped. Adding CASCADE to the end of the drop statement results in an error in my case. If I drop constraint t1_fk first and then t3_uk the unique index is also dropped. So, sounds like if you define the foreign key constraint after the unique constraint that includes that FK field then you must drop the FK constraint first and then the unique constraint in order for the unique index to also be dropped. On Friday, April 24, 2020 at 9:22:04 AM UTC-5, Russ Jackson wrote: > > re: h2database 1.4.196 > > We are noticing that the unique index generated for a unique constraint > that includes a foreign keyed column is also being used as the index for > that foreign key constraint. Is this expected or should the foreign key > get it's own index? > > We noticed this because when dropping the unique constraint the auto > generated unique index was not being dropped. > > But, if we first drop the foreign key constraint and then the unique > constraint the auto generated unique index is dropped. > > Thanks. > -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To view this discussion on the web visit https://groups.google.com/d/msgid/h2-database/0e4ad085-38f8-4fea-8222-bbcfd78f02d6%40googlegroups.com.
