cannot create table with foreign key in mysql

Question

I know this question is posted a lot, and I've checked my code carefully but couldn't find the reason why when I create the table with Foreign Key, mysql gives me an error.

mysql> CREATE TABLE Act_Model(
    -> code VARCHAR(8) NOT NULL,
    -> model VARCHAR(64) NOT NULL,
    -> PRIMARY KEY (code))ENGINE = INNODB;
Query OK, 0 rows affected (0.09 sec)
mysql> CREATE TABLE IBT_ActItem(
    -> model VARCHAR(64) NOT NULL,
    -> flagbit BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    -> PRIMARY KEY(model),
    -> FOREIGN KEY( model) REFERENCES Act_Model(model))ENGINE = INNODB;
ERROR 1005 (HY000): Can't create table 'test.ibt_actitem' (errno: 150)
mysql> CREATE TABLE IBT_ActItem(
    -> model VARCHAR(64) NOT NULL,
    -> flagbit BIGINT(20) UNSIGNED NOT NULL DEFAULT '0',
    -> PRIMARY KEY(model))ENGINE = INNODB;
Query OK, 0 rows affected (0.09 sec)

when I used show engins;, for InnoDB it gave me:

| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |

Can you help me to find where is my mistake? Thanks

Are you sure you want it that way round? Looks a bit strange to me. — user330315
– user330315, Commented Jan 11, 2013 at 18:35
@ Adem: I followed your advice to SET foreign_key_checks = 0;, then drop the tables, and then create the tables again. Still the same error. — HuongOrchid
– HuongOrchid, Commented Jan 11, 2013 at 18:36
@ njk: ALTER TABLE IBT_ActItem ADD CONSTRAINT IBT_ActItem FOREIGN KEY (model) REFERENCES Act_Model (model); ERROR 1005 (HY000): Can't create table 'test.#sql-c3_ae08' (errno: 150) — HuongOrchid
– HuongOrchid, Commented Jan 11, 2013 at 18:37

Ike Walker · Accepted Answer · 2013-01-11 18:29:47Z

4

You need to add a unique index on the model column in the parent table:

CREATE TABLE Act_Model(
 code VARCHAR(8) NOT NULL,
 model VARCHAR(64) NOT NULL,
 PRIMARY KEY (code),
 UNIQUE KEY model (model)
 )ENGINE = INNODB;

answered Jan 11, 2013 at 18:29

Ike Walker

65.8k14 gold badges115 silver badges112 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

spencer7593 Over a year ago

NOTE: this will enforce a "one -to- zero-or-one" relationship between the two tables.

spencer7593 · Accepted Answer · 2013-01-11 18:52:13Z

MySQL requires that you have a UNIQUE KEY or PRIMARY KEY constraint on the column(s) that is the target of the FOREIGN KEY constraint.

To add a unique key, you can use this syntax:

ALTER TABLE Act_Model ADD CONSTRAINT Act_Model_UX UNIQUE KEY (model) ;

You already have the model column defined as the PRIMARY KEY in the IBT_ActItem table.

If you add a UNIQUE KEY constraint and a FOREIGN KEY constraint on the model column of the other table (Act_Model), you are in effect specifying a "one-to-one(optional)" relationship between these two tables.

The UNIQUE (or PRIMARY) KEY goes on the parent table, which is the "one" side of the "one-to-many" relationship. The FOREIGN KEY goes on the child table, which is the "zero, one,or many" side of the relationship.

(The normative practice is to have a foreign key reference the primary key of the target table. To make the model column, rather than the code column, the primary key:

ALTER TABLE Act_Model DROP PRIMARY KEY ;
ALTER TABLE Act_Model ADD PRIMARY KEY (model) ;

NOTE: There are some significant differences between a UNIQUE KEY and the PRIMARY KEY. For example, there can only be one PRIMARY KEY defined on a table, and the PRIMARY KEY constraint enforces a NOT NULL constraint on the column.)

Collectives™ on Stack Overflow

cannot create table with foreign key in mysql

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related