외래키 (Foreign Keys)

개요

외래키는 데이터 무결성을 적용하는 데 사용할 수 있는 제약 조건입니다. 이것은 부모 테이블의 단일 컬럼(또는 다중 컬럼)을 참조하는 자식 테이블의 컬럼(또는 다중 컬럼)으로 구성됩니다. 외래 키가 사용되는 경우, MariaDB는 일부 무결성 규칙이 항상 적용되도록 몇 가지 검사를 수행합니다. 자세한 설명은 다음을 참조하십시오, 참조 Relational databases: Foreign Keys.

외래키는 이를 지원하는 스토리지 엔진에서만 사용할 수 있습니다. 기본적으로 InnoDB 엔진 그리고 더이상 사용되지 않는 PBXT 엔진이 외래키를 지원합니다.

Partitioned tables (파티션으로 구성된 테이블)은 외래키를 포함할 수 없으며 외래키로 참조할 수도 없습니다.

구문

CREATE TABLE b(for_key INT REFERENCES a(not_key));

외래키는 CREATE TABLE 또는 ALTER TABLE 문으로 생성할 수 있습니다. 정의는 다음 구문을 따라야 합니다:

[CONSTRAINT [symbol]] FOREIGN KEY
    [index_name] (index_col_name, ...)
    REFERENCES tbl_name (index_col_name,...)
    [ON DELETE reference_option]
    [ON UPDATE reference_option]

reference_option:
    RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULT

이 symbol절은 지정된 경우 오류 메시지에 사용되며 데이터베이스에서 고유해야 합니다.

자식 테이블의 열은 인덱스이거나 인덱스의 가장 왼쪽 부분이어야 합니다. 인덱스 접두사는 지원되지 않습니다(따라서 TEXT 및 BLOB 열은 외래 키로 사용할 수 없음). MariaDB가 외래 키에 대한 인덱스를 자동으로 생성하는 경우(존재하지 않고 명시적으로 생성되지 않은 경우) index_name은 symbol의 해당 이름으로 명명됩니다.

참조된 열은 PRIMARY KEY 또는 UNIQUE 인덱스여야 합니다.

외래 키 열과 참조 열 모두 PERSISTENT 열 이 될 수 있습니다 . 그러나 이 경우 ON UPDATE CASCADE, ON UPDATE SET NULL, ON DELETE SET NULL 절은 허용되지 않습니다.

외래 키 열과 참조 열은 동일한 유형이거나 유사한 유형이어야 합니다. 정수 유형의 경우 크기와 부호도 동일해야 합니다.

상위 및 하위 테이블은 동일한 스토리지 엔진을 사용해야 하며 TEMPORARY 또는 파티셔닝된(partitioned) 테이블이 아니어야 합니다 . 상위와 하위 테이블은 같은 테이블 일수도 있습니다.

제약

외래 키가 있는 경우 자식 테이블의 각 행은 부모 테이블의 행과 일치해야 합니다. 여러 하위 행이 동일한 상위 행과 일치할 수 있습니다. 모든 외래 키 값이 상위 테이블의 상위 행 값과 동일한 경우 하위 행 은 상위 행 과 일치 합니다. 그러나 외래 키 값 중 하나 이상이 NULL이면 행에 부모가 없지만 여전히 허용됩니다.

외래 키가 있는 경우 자식 테이블의 각 행은 부모 테이블의 행과 일치해야 합니다.여러 하위 행이 동일한 상위 행과 일치할 수 있습니다. A child row matches a parent row if all its foreign key values are identical to a parent row's values in the parent table. 그러나, 외래키에 NULL을 허용할 경우 부모키는 NULL이 없겠지만 NULL값이 들어 갈 수 있습니다.

MariaDB performs certain checks to guarantee that the data integrity is enforced:

• Trying to insert non-matching rows (or update matching rows in a way that makes them non-matching rows) in the child table produces a 1452 error (SQLSTATE '23000').
• When a row in the parent table is deleted and at least one child row exists, MariaDB performs an action which depends on the ON DELETE clause of the foreign key.
• When a value in the column referenced by a foreign key changes and at least one child row exists, MariaDB performs an action which depends on the ON UPDATE clause of the foreign key.
• Trying to drop a table that is referenced by a foreign key produces a 1217 error (SQLSTATE '23000').
• A TRUNCATE TABLE against a table containing one or more foreign keys is executed as a DELETE without WHERE, so that the foreign keys are enforced for each row.

The allowed actions for ON DELETE and ON UPDATE are:

• RESTRICT: The change on the parent table is prevented. The statement terminates with a 1451 error (SQLSTATE '2300'). This is the default behavior for both ON DELETE and ON UPDATE.
• NO ACTION: Synonym for RESTRICT.
• CASCADE: The change is allowed and propagates on the child table. For example, if a parent row is deleted, the child row is also deleted; if a parent row's ID changes, the child row's ID will also change.
• SET NULL: The change is allowed, and the child row's foreign key columns are set to NULL.
• SET DEFAULT: Only worked with PBXT. Similar to SET NULL, but the foreign key columns were set to their default values. If default values do not exist, an error is produced.

The delete or update operations triggered by foreign keys do not activate triggers and are not counted in the Com_delete and Com_update status variables.

Foreign key constraints can be disabled by setting the foreign_key_checks server system variable to 0. This speeds up the insertion of large quantities of data.

Metadata

The Information Schema REFERENTIAL_CONSTRAINTS table contains information about foreign keys. The individual columns are listed in the KEY_COLUMN_USAGE table.

The InnoDB-specific Information Schema tables also contain information about the InnoDB foreign keys. The foreign key information is stored in the INNODB_SYS_FOREIGN. Data about the individual columns are stored in INNODB_SYS_FOREIGN_COLS.

The most human-readable way to get information about a table's foreign keys sometimes is the SHOW CREATE TABLE statement.

제한 사항

Foreign keys have the following limitations in MariaDB:

• Currently, foreign keys are only supported by InnoDB.
• Cannot be used with views.
• The SET DEFAULT action is not supported.
• Foreign keys actions do not activate triggers.
• If ON UPDATE CASCADE recurses to update the same table it has previously updated during the cascade, it acts like RESTRICT.

예

Let's see an example. We will create an author table and a book table. Both tables have a primary key called id. book also has a foreign key composed by a field called author_id, which refers to the author primary key. The foreign key constraint name is optional, but we'll specify it because we want it to appear in error messages: fk_book_author.

CREATE TABLE author (
  id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  name VARCHAR(100) NOT NULL
) ENGINE = InnoDB;

CREATE TABLE book (
  id MEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  title VARCHAR(200) NOT NULL,
  author_id SMALLINT UNSIGNED NOT NULL,
  CONSTRAINT `fk_book_author`
    FOREIGN KEY (author_id) REFERENCES author (id)
    ON DELETE CASCADE
    ON UPDATE RESTRICT
) ENGINE = InnoDB;

Now, if we try to insert a book with a non-existing author, we will get an error:

INSERT INTO book (title, author_id) VALUES ('Necronomicon', 1);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

The error is very descriptive.

Now, let's try to properly insert two authors and their books:

INSERT INTO author (name) VALUES ('Abdul Alhazred');
INSERT INTO book (title, author_id) VALUES ('Necronomicon', LAST_INSERT_ID());

INSERT INTO author (name) VALUES ('H.P. Lovecraft');
INSERT INTO book (title, author_id) VALUES
  ('The call of Cthulhu', LAST_INSERT_ID()),
  ('The colour out of space', LAST_INSERT_ID());

It worked!

Now, let's delete the second author. When we created the foreign key, we specified ON DELETE CASCADE. This should propagate the deletion, and make the deleted author's books disappear:

DELETE FROM author WHERE name = 'H.P. Lovecraft';

SELECT * FROM book;
+----+--------------+-----------+
| id | title        | author_id |
+----+--------------+-----------+
|  3 | Necronomicon |         1 |
+----+--------------+-----------+

We also specified ON UPDATE RESTRICT. This should prevent us from modifying an author's id (the column referenced by the foreign key) if a child row exists:

UPDATE author SET id = 10 WHERE id = 1;
ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails 
 (`test`.`book`, CONSTRAINT `fk_book_author` FOREIGN KEY (`author_id`) 
  REFERENCES `author` (`id`) ON DELETE CASCADE)

참조

Until MariaDB 10.4

CREATE TABLE a(a_key INT primary key, not_key INT);

CREATE TABLE b(for_key INT REFERENCES a(not_key));

SHOW CREATE TABLE b;
+-------+----------------------------------------------------------------------------------+
| Table | Create Table                                                                     |
+-------+----------------------------------------------------------------------------------+
| b     | CREATE TABLE `b` (
  `for_key` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------+

INSERT INTO a VALUES (1,10);
Query OK, 1 row affected (0.005 sec)

INSERT INTO b VALUES (10);
Query OK, 1 row affected (0.004 sec)

INSERT INTO b VALUES (1);
Query OK, 1 row affected (0.004 sec)

SELECT * FROM b;
+---------+
| for_key |
+---------+
|      10 |
|       1 |
+---------+

From MariaDB 10.5

CREATE TABLE a(a_key INT primary key, not_key INT);

CREATE TABLE b(for_key INT REFERENCES a(not_key));
ERROR 1005 (HY000): Can't create table `test`.`b` 
  (errno: 150 "Foreign key constraint is incorrectly formed")

CREATE TABLE c(for_key INT REFERENCES a(a_key));

SHOW CREATE TABLE c;
+-------+----------------------------------------------------------------------------------+
| Table | Create Table                                                                     |
+-------+----------------------------------------------------------------------------------+
| c     | CREATE TABLE `c` (
  `for_key` int(11) DEFAULT NULL,
  KEY `for_key` (`for_key`),
  CONSTRAINT `c_ibfk_1` FOREIGN KEY (`for_key`) REFERENCES `a` (`a_key`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+----------------------------------------------------------------------------------+

INSERT INTO a VALUES (1,10);
Query OK, 1 row affected (0.004 sec)

INSERT INTO c VALUES (10);
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails 
  (`test`.`c`, CONSTRAINT `c_ibfk_1` FOREIGN KEY (`for_key`) REFERENCES `a` (`a_key`))

INSERT INTO c VALUES (1);
Query OK, 1 row affected (0.004 sec)

SELECT * FROM c;
+---------+
| for_key |
+---------+
|       1 |
+---------+

See Also

• MariaDB: InnoDB foreign key constraint errors, a post in the MariaDB blog