Jungsu, ----- Original Message ----- From: ""Heo, Jungsu"" <[EMAIL PROTECTED]> Newsgroups: mailing.database.mysql Sent: Saturday, September 28, 2002 9:34 AM Subject: Problem on "ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY.."
> Hello. > > I have a problem on "ALTER TABLE ... ADD FOREIGN KEY". > > I'm using InnoDBs, and I wanna create Foreign Key after table has been created. > > But, 'ALTER TABLE .. ADD FOREIGN KEY" does not work. > > Manual says : > > The FOREIGN KEY, CHECK, and REFERENCES clauses don't actually do anything > > And in my test, 'ALTER .... ADD FOREIGN KEY' does not create FK. > > mysql> CREATE TABLE `CONS_PARENT` ( > -> `A` int(11) NOT NULL default '0', > -> `B` int(11) NOT NULL default '0', > -> UNIQUE KEY `A` (`A`,`B`) ) Type=InnoDB ; > Query OK, 0 rows affected (0.02 sec) > > mysql> CREATE TABLE `CONS_CHILD` ( > -> `A` int(11) NOT NULL default '0', > -> `B` int(11) NOT NULL default '0', > -> KEY `A` (`A`,`B`) > -> ) TYPE=InnoDB ; > Query OK, 0 rows affected (0.05 sec) > > mysql> ALTER TABLE CONS_CHILD ADD CONSTRAINT FOREIGN KEY CONS_CHILD_FK ( A , B ) > REFERENCES CONS_PARENT ON DELETE CASCADE ; > Query OK, 0 rows affected (0.05 sec) > Records: 0 Duplicates: 0 Warnings: 0 > > # See Below! FK does not EXIST > mysql> SHOW CREATE TABLE CONS_CHILD ; > +------------+-------------------------------------------------------------- ---- > ---------------------------------------------------------------------+ > | Table | Create Table > | > +------------+-------------------------------------------------------------- ---- > ---------------------------------------------------------------------+ > | CONS_CHILD | CREATE TABLE `CONS_CHILD` ( > `A` int(11) NOT NULL default '0', > `B` int(11) NOT NULL default '0', > KEY `A` (`A`,`B`) > ) TYPE=InnoDB | > +------------+-------------------------------------------------------------- ---- > ---------------------------------------------------------------------+ > 1 row in set (0.00 sec) the problem above is that the syntax of the ALTER TABLE is wrong for InnoDB. InnoDB uses the syntax of SQL-1992 with the restriction that InnoDB always requires that also the columns in the referenced table are always explicitly named. No name can be given to the foreign key constraint. The MySQL parser approves the constraint, though, because the syntax in MySQL is less restrictive. Hmm... I have to add the extended foreign key syntax to InnoDB-4.0.x. InnoDB-4.0.5 will give a table handler error 150 if the syntax is not ok. That will warn the user. I will also update the MySQL-4.0 manual. http://www.innodb.com/ibman.html#InnoDB_foreign_keys " Starting from version 3.23.50 InnoDB allows you to add a new foreign key constraint to a table through ALTER TABLE yourtablename ADD CONSTRAINT FOREIGN KEY (...) REFERENCES anothertablename(...) ... Remember to create the required indexes first, though. You can also add a self-referential foreign key constraint to a table using ALTER TABLE. " >From a SQL-1992 draft: " 11.8 <referential constraint definition> Function Specify a referential constraint. Format <referential constraint definition> ::= FOREIGN KEY <left paren> <referencing columns> <right paren> <references specification> <references specification> ::= REFERENCES <referenced table and columns> [ MATCH <match type> ] [ <referential triggered action> ] <match type> ::= FULL | PARTIAL <referencing columns> ::= <reference column list> <referenced table and columns> ::= <table name> [ <left paren> <reference column list> <right paren> ] <reference column list> ::= <column name list> <referential triggered action> ::= <update rule> [ <delete rule> ] | <delete rule> [ <update rule> ] <update rule> ::= ON UPDATE <referential action> <delete rule> ::= ON DELETE <referential action> <referential action> ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION " > Does any body know how can create FK after tables creates? I rewrote the ALTER TABLE in the InnoDB syntax and tested 4.0.4: mysql> ALTER TABLE CONS_CHILD ADD CONSTRAINT FOREIGN KEY ( A , B ) -> REFERENCES CONS_PARENT (A, B) ON DELETE CASCADE ; Query OK, 0 rows affected (0.00 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> mysql> SHOW CREATE TABLE CONS_CHILD ; +------------+-------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- --------------------------------------------------------------------------+ | Table | Create Table | +------------+-------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- --------------------------------------------------------------------------+ | CONS_CHILD | CREATE TABLE `CONS_CHILD` ( `A` int(11) NOT NULL default '0', `B` int(11) NOT NULL default '0', KEY `A` (`A`,`B`), FOREIGN KEY (`A`, `B`) REFERENCES `test.CONS_PARENT` (`A`, `B`) ON DELETE CASC ADE ) TYPE=InnoDB | +------------+-------------------------------------------------------------- ---- ---------------------------------------------------------------------------- ---- --------------------------------------------------------------------------+ 1 row in set (0.00 sec) > Thank you. Have a nice weekend! Thank you! Heikki Innobase Oy sql query --------------------------------------------------------------------- Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
