Geek Answers Handbook

MySQL: broken foreign key?

The built-in syntax for defining foreign keys in MySQL does not seem to do anything, while the longer CONSTRAINT syntax works as expected. I am very curious why this is happening.

I recently discovered that the built-in syntax for defining foreign keys, which I thought would work fine, does not perform any real referential integrity check. It doesn't matter what is defined in the ON DELETE / UPDATE clauses.
Of course, I tried this with InnoDB, since I know that MyISAM does not support foreign key checks.

Please see the examples / scripts and you will see what I mean.

Inline syntax

This is the correct way to define foreign keys according to the documentation that I have used for many years.

-- Create a basic foreign key relationship.
CREATE TABLE `parent` (
  `id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `a` VARCHAR(255) NOT NULL
)ENGINE=InnoDB; -- Just to be sure.

CREATE TABLE `child` (
  `id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  -- Short legitimate syntax, frequently used.
  `parent_id` INTEGER UNSIGNED NOT NULL REFERENCES `parent` (`id`)
    ON DELETE RESTRICT
)ENGINE=InnoDB; -- Just to be sure.

Now, if I remove the row from the table parent , which refers to table a subsidiary , it is removed in the same way as I did not specify the foreign key. Try it Yourself: Fiddle

Syntax CONSTRAINT

The longer syntax using the CONSTRAINT keyword is more cumbersome to write, but seems to work as expected, unlike the built-in definition.

-- `parent` table has been omitted, since it is the same as above.
CREATE TABLE `child` (
  `id` INTEGER UNSIGNED PRIMARY KEY AUTO_INCREMENT,
  `parent_id` INTEGER UNSIGNED NOT NULL,
  -- Longer, more cumbersome syntax.
  CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
    ON DELETE RESTRICT
)ENGINE=InnoDB; -- Just to be sure.

If I try to remove the reference line from parent , it will fail. Try it for yourself: Fiddle (you need to uncomment the last statement on the left to see it crash)

Conclusion / Actual Question

, , , , CONSTRAINT .

- , ? - MySQL, ?
, , .

/SHOW CREATE TABLE Verification

SHOW CREATE TABLE, @MikePurcell.

""

CREATE TABLE `child`(
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

CONSTRAINT

CREATE TABLE `child` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `parent_id` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_child_parent` (`parent_id`),
  CONSTRAINT `fk_child_parent` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8

, REFERENCES "short". , . .

FYI: 2004 ( # 4919) , , . , , , , , .

+4
1

" ", , :

create_definition:
    col_name column_definition
...
    | [CONSTRAINT [symbol]] FOREIGN KEY
      [index_name] (index_col_name,...) reference_definition

reference_definition:
    REFERENCES tbl_name (index_col_name,...)
      [MATCH FULL | MATCH PARTIAL | MATCH SIMPLE]
      [ON DELETE reference_option]
      [ON UPDATE reference_option]

FOREIGN KEY.

" ", , :

column_definition:
    data_type [NOT NULL | NULL] [DEFAULT default_value]
...
      [reference_definition]

FOREIGN KEY.

:

MySQL , " REFERENCES " (as SQL), . MySQL REFERENCES FOREIGN KEY.

.

, .

+2

More articles:


All Articles