Re: Foreign key on multiple columns
On 2013-03-21 8:12 AM, Norah Jones wrote: I'm trying to create a foreign key on two columns but getting error... Here's what I tried: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; But I get `ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)` Dupe reference column. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key on multiple columns
On 3/21/2013 12:43 PM, Abhishek Choudhary wrote: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; i think error is because of referencing the same column in test1 table (ID,ID) . try to change the column name then run the code hope ur problem will solve out . Another solution would be to make two FK declarations, one for each column. CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1) REFERENCES test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT , CONSTRAINT fk2 FOREIGN KEY (col2) REFERENCES test1(ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; This is the preferred syntax and it meets your original intent of associating both col1 and col2 to the ID column of the other table. -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Foreign key on multiple columns
CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; i think error is because of referencing the same column in test1 table (ID,ID) . try to change the column name then run the code hope ur problem will solve out . Abhishek choudhary, www.tech4urhelp.blogspot.com - Original Message - From: Peter Brawley peter.braw...@earthlink.net To: mysql@lists.mysql.com Cc: Sent: Thursday, 21 March 2013 8:14 PM Subject: Re: Foreign key on multiple columns On 2013-03-21 8:12 AM, Norah Jones wrote: I'm trying to create a foreign key on two columns but getting error... Here's what I tried: CREATE TABLE test2 ( ID INT NOT NULL AUTO_INCREMENT, col1 INT NOT NULL, col2 INT NOT NULL, PRIMARY KEY (ID), CONSTRAINT fk FOREIGN KEY (col1, col2) REFERENCES test1(ID, ID) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB; But I get `ERROR 1005 (HY000): Can't create table 'DB.test2' (errno: 150)` Dupe reference column. PB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
Re: Can i create a composite foreign key(on multiple columns) in a table.
san [EMAIL PROTECTED] wrote:
Is it possible to create a composite foreign key (on multiple columns
as in primary key enabled tables) in a table.
MySQl manuals defines the syntax as:
The syntax of a foreign key constraint definition in InnoDB:
[CONSTRAINT symbol] *FOREIGN KEY (index_col_name, ...)*
REFERENCES table_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
Yes, you can do it.
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Egor Egorov
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can i create a composite foreign key(on multiple columns) in a table.
The InnoDB manual (below) doesn't mention it. Why not try and let us know?
http://www.innodb.com/ibman.html#InnoDB_foreign_keys
Edward Dudlik
Becoming Digital
www.becomingdigital.com
- Original Message -
From: san [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, 06 June, 2003 09:05
Subject: Can i create a composite foreign key(on multiple columns) in a table.
Hi,
Is it possible to create a composite foreign key (on multiple columns
as in primary key enabled tables) in a table.
MySQl manuals defines the syntax as:
The syntax of a foreign key constraint definition in InnoDB:
[CONSTRAINT symbol] *FOREIGN KEY (index_col_name, ...)*
REFERENCES table_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
Kindly give solutions to my query.
Thanks for your time
Regards
San
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can i create a composite foreign key(on multiple columns) in a table.
Hi,
Is it possible to create a composite foreign key (on multiple columns
as in primary key enabled tables) in a table.
MySQl manuals defines the syntax as:
The syntax of a foreign key constraint definition in InnoDB:
[CONSTRAINT symbol] *FOREIGN KEY (index_col_name, ...)*
REFERENCES table_name (index_col_name, ...)
[ON DELETE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
[ON UPDATE {CASCADE | SET NULL | NO ACTION
| RESTRICT}]
Kindly give solutions to my query.
Thanks for your time
Regards
San
