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

Twitter Blog Facebook LinkedIn Google+