3

I am trying to add foreign keys to my table but receiving this error. Error Code: 1005 Can't create table 'william.#sql-88c_3' (errno: 150) I have 3 tables. employee, client and Contract.

employe [employee_no PK] , Client[customer_no PK] contract [contract_no PK] I want to have Foreign keys for contract as contract [contract_no PK, employee_no FK], customer_no FK]

I tried to do directly it failed, I am now trying the alter statement.Is anything wrong with the Alter script?

 ALTER TABLE contract ADD CONSTRAINT `employee_no_fk2` FOREIGN KEY (`employee_no`) REFERENCES `employee` (`employee_no`); ALTER TABLE contract ADD CONSTRAINT `Customer_no_fk2` FOREIGN KEY (`Customer_no`) REFERENCES `client` (`Customer_no`);
Improve this question
0

4 Answers 4

Reset to default
10

Most of such error will be related to data type miss match or so.. If you could go through these links.. it might help you i guess.. Check-this ... also Check-this

As they say in the second link:

The first place you should look is whether the data types agree between the foreign key and primary key columns.

mysql> SHOW engine innodb STATUS; ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 100130 17:16:57 Error IN FOREIGN KEY CONSTRAINT OF TABLE sampledb/#sql-4a0_2: FOREIGN KEY(member_type) REFERENCES common_lookup(common_lookup_id): Cannot find an INDEX IN the referenced TABLE WHERE the referenced COLUMNS appear AS the FIRST COLUMNS, OR COLUMN types IN the TABLE AND the referenced TABLE do NOT MATCH FOR CONSTRAINT.
Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Second link was very helpful. I was trying to add a foreign key with on delete set null, but my target column was having not null constraint, which was giving me this problem. I could figure out issue with the SHOW engine innodb STATUS; command.
Second link ftw! I was doing as he described: "The most common variation that I’ve run into is where the primary key column uses a int unsigned data type and the foreign key column uses an int data type. It’s quite nice that the InnoDB Engine stops this cold. Naturally, you fix it by changing the foreign key data type to match the int unsigned data type."
2

make sure that one of the key field that you are trying to reference does not have an index and/or is not a primary key. and the two key fields type and/or size should be an exact match also make sure that both tables are InnoDB tables.

Improve this answer

Comments

1

This can happen due to two reasons

  1. Table creation failed because a foreign key constraint was not correctly formed or
  2. Datatype mismatch in the constraints.

the below link would be helpful

http://dev.mysql.com/doc/refman/5.0/en/innodb-error-codes.html

Improve this answer

Comments

0

Last time I encountered this, it was the constraints: referenced table key type was 'int' and referring table had 'unsigned int' referring field by mistake, instead of int.

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.