Re: [Maria-developers] [Commits] Rev 3966: MDEV-4439 ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS does not work if constraint name is not used. in file:///home/hf/wmar/10-hf/
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); 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); R:
At file:///home/hf/wmar/10-hf/
------------------------------------------------------------ revno: 3966 revision-id: holyfoot@askmonty.org-20140201114304-7bi0ug2yg3eyyhlr parent: psergey@askmonty.org-20140121100700-9g8hl8vx8nmk1e2w committer: Alexey Botchkov <holyfoot@askmonty.org> branch nick: 10-hf timestamp: Sat 2014-02-01 15:43:04 +0400 message: MDEV-4439 ALTER TABLE .. [ADD|DROP] FOREIGN KEY IF [NOT] EXISTS does not work if constraint name is not used. Patches for server and the Innodb engine. Server is fixed so it does nothing if no indexes left to alter. Innodb parser is fixed so it looks for the IF [NOT] EXISTS option in a string. Another change is that it uses the index name for the internal dictionary. Prior to that it only used the CONSTRAINT name for it.
_______________________________________________ commits mailing list commits@mariadb.org https://lists.askmonty.org/cgi-bin/mailman/listinfo/commits
-- -- 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>
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. 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. By the way, similar tests are possible for DROP too. Regards, Sergei
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>
Hi, Jan!
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.
Hmm. I didn't know that, I thought InnoDB only generates a name when no was explicitly given by the user. What names will be shown in SHOW CREATE TABLE and various I_S tables? I mean, "a" and "b" or generated ones? Regards, Sergei
participants (2)
-
Jan Lindström -
Sergei Golubchik