Hi,

Hi, Jan!

Good point!

On Feb 02, Jan Lindström wrote:

...

Hi,

In my opinion the test case is not complete, you are missing e.g.

ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS (id) REFERENCES t1(id); -- should fail on warning ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS (id) REFERENCES t1(id); I suppose in the current implementation "IF NOT EXISTS" clause won't help, because the FK name is generated in the engine.

Yes, exactly.

Holyfoot, perhaps it'd still make sense to move the check down into the engine?

...

ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS a(id REFERENCES t1(id); -- should fail, there is already foreign key for id, yes it name is not b or a but anyway ALTER TABLE t2 ADD FOREIGN KEY IF NOT EXISTS b(id) REFERENCES t1(id); Detecting identically defined foreign key constraints isn't part of this task. This task only operates with object names.

How you would detect the difference between no object name given and object name given, remember that InnoDB does not store the key identifier (a and b above). Thus the actual foreign key name is similar to one generated when no key identifier is not given ? E.g: alter table t2 add foreign key if not exists (id) references t1 (id); alter table t2 add foreign key if not exists a(id) references t1(id); alter table t2 add foreign key if not exists b(id) references t1(id); For InnoDB point of view there are all similar foreign keys but all have different object name and all actual names are generated on the engine. R: Jan -- -- Jan Lindström Principal Engineer MariaDB | MaxScale | skype: jan_p_lindstrom www.skysql.com http://www.skysql.com/ Twitter http://twitter.com/skysql Blog http://www.skysql.com/blog/ Facebook http://www.facebook.com/skysql LinkedIn http://www.linkedin.com/company/1214250 Google+ https://plus.google.com/117544963211695643458/posts