CODEWITHSUNDEEP
mysqlsqlforeign-keysforeign-key-relationship
devdar
devdar

Reputation: 5654

MySql Composite Foreign Key Referncing same column

I am attempting to create a composite foreign key in MySQL however both fields are referencing the same column in another table. I am not sure if this is the accurate approach since the sql is not executing. Under is the SQL statement

SQL 

ALTER TABLE tableA ADD CONSTRAINT `comp_fk`
    FOREIGN KEY (`a_id` , `b_id` )
    REFERENCES `tabelB` (`p_id` , `p_id` )
    ON DELETE NO ACTION
    ON UPDATE NO ACTION;

Error

MySQL Database Error: Can't create table 'sep.#sql-984_8' (errno: 150)

Upvotes: 0

Views: 61

Answers (1)

M Khalid Junaid
M Khalid Junaid

Reputation: 64466

Try this by individually applying constraint

ALTER TABLE `comp_fk`  
  ADD CONSTRAINT `test` FOREIGN KEY (`a_id`) REFERENCES `tabelB`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION,
  ADD CONSTRAINT `test2` FOREIGN KEY (`b_id`) REFERENCES `tabelB`(`id`) ON UPDATE NO ACTION ON DELETE NO ACTION

Upvotes: 1

Related Questions