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
