MySQL: no foreign key constraint is applied
I have two tables:
CREATE TABLE customer
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(25),
PRIMARY KEY(id)
);
CREATE TABLE `client`
(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(200),
`customer_id` INT NOT NULL,
PRIMARY KEY(`id`),
INDEX(`customer_id`),
FOREIGN KEY (`customer_id`) REFERENCES `customer`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
);
Then I ran the following:
INSERT INTO customer (name) VALUES ('Customer1');
Now table client contains name: Customer1, id: 1
Then I ran this:
INSERT INTO client (name, customer_id) VALUES ('Client of Customer1',34);
It was supposed to fail, but it inserted successfully. Why is this?
This is on MySQL 5.1 in Linux Mint.
+3
source to share
2 answers
Do it show create table customer
. It will show the motor used at the end of the reset statement create table
. If they appear as MyISAM, then this engine does not support foreign keys. FK definitions are parsed but ignored otherwise.
To force the table to be Inno DB that supports foren keys, you need to do
CREATE TABLE ( ... blah blah blah ...) TYPE=InnoDB;
^^^^^^^^^^^--force InnoDB type
+3
source to share
There might be an engine issue, just to add the engine when creating tables:
CREATE TABLE customer
(
id INT NOT NULL AUTO_INCREMENT,
name VARCHAR(25),
PRIMARY KEY(id)
) ENGINE=INNODB;
CREATE TABLE `client`
(
`id` INT NOT NULL AUTO_INCREMENT,
`name` VARCHAR(200),
`customer_id` INT NOT NULL,
PRIMARY KEY(`id`),
INDEX(`customer_id`),
FOREIGN KEY (`customer_id`) REFERENCES `customer`(`id`) ON UPDATE CASCADE ON DELETE RESTRICT
) ENGINE=INNODB;
+1
source to share