When you specify a foreign key the table referenced by the foreign key is the?
Problem:You want to create a foreign key for a table in a database. Show Example:We would like to create a table named student that contains a foreign key that refers to the id column in the table city. Solution 1 (new table):CREATE TABLE student ( id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, city_id INT FOREIGN KEY REFERENCES city(id) );Discussion:To create a new table containing a foreign key column that references another table, use the keyword FOREIGN KEY REFERENCES at the end of the definition of that column. Follow that with the name of the referenced table and the name of the referenced column in parentheses. In our example, we create the table student using a CREATE TABLE clause. We list the columns’ names and put their respective data types in parentheses. The column city_id is the foreign key in this table and indicates the value of the ID stored in the column id in the table city. We write FOREIGN KEY REFERENCES at the end of the definition of this column and follow it with the referenced table and column: city(id). Keep in mind that you can create more than one foreign key for a table. Solution 2 (new table):CREATE TABLE student ( id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, city_id INT, FOREIGN KEY (city_id) REFERENCES city(id) );Discussion:Another way to define a foreign key during table creation is to use the FOREIGN KEY REFERENCES clause at the end of the column definitions. In this case, after the FOREIGN KEY clause, we designate the foreign key column. Next comes the REFERENCES clause along with the name of the referred table and column. You can create foreign keys on more than one column, as shown below: Solution 3 (new table):CREATE TABLE student ( id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, score_id INT, subject_id INT, CONSTRAINT fk_student_score_subject_id FOREIGN KEY (subject_id, score_id) REFERENCES score_subject(subject_id, score_id) );In this example, the constraint fk_student_score_subject_id is a foreign key consisting of two columns: score_id and subject_id. These two foreign key columns refer to two columns in the table score_subject – score_id and subject_id. Here’s another example: Solution 4 (new table):CREATE TABLE student ( id INT PRIMARY KEY, first_name VARCHAR(100) NOT NULL, last_name VARCHAR(100) NOT NULL, city_id INT, CONSTRAINT fk_student_city_id FOREIGN KEY (city_id) REFERENCES city(id) );Discussion:In this code, we again have the CONSTRAINT clause with the name of this constraint. Use names that are easy to read and understand. In our example, we use the name fk_student_city_id, which indicates the relevant table and column. Next, we write FOREIGN KEY and add (in parentheses) the name of the column that becomes the foreign key. Then we have the REFERENCES clause followed by the name of the referenced table and column (here: id). Solution 5 (existing table):ALTER TABLE student ADD FOREIGN KEY (city_id) REFERENCES city(id);Discussion:It is also possible to add a new foreign key to an existing table. Here, the table is altered using an ALTER TABLE clause. The table name (in our example, student) is placed after the ALTER TABLE keyword. Next, the ADD FOREIGN KEY clause is followed by the name of the column that will be used as the foreign key. Then we have the REFERENCES clause with the name of the referenced table and the name of the primary key column in parentheses. Note that the table you’re modifying must exist before this command is executed. Solution 6 (existing table, foreign key constraint):ALTER TABLE student ADD CONSTRAINT fk_student_city_id FOREIGN KEY (city_id) REFERENCES city(id)Discussion:Use a query like this if you want to name a foreign key column as a constraint for an existing table. Here, the foreign key constraint is named fk_student_city_id. If you do not specify the constraint name, the database generates a default constraint name (which will vary by database). 13.1.20.5 FOREIGN KEY ConstraintsMySQL supports foreign keys, which permit cross-referencing related data across tables, and foreign key constraints, which help keep the related data consistent. A foreign key relationship involves a parent table that holds the initial column values, and a child table with column values that reference the parent column values. A foreign key constraint is defined on the child table. The essential syntax for a defining a foreign key constraint in a CREATE TABLE or ALTER TABLE statement includes the following: [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...) REFERENCES tbl_name (col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION | SET DEFAULTForeign key constraint usage is described under the following topics in this section:
IdentifiersForeign key constraint naming is governed by the following rules:
Table and column identifiers in a FOREIGN KEY ... REFERENCES clause can be quoted within backticks (`). Alternatively, double quotation marks (") can be used if the ANSI_QUOTES SQL mode is enabled. The lower_case_table_names system variable setting is also taken into account. Conditions and RestrictionsForeign key constraints are subject to the following conditions and restrictions:
For information about how the MySQL implementation of foreign key constraints differs from the SQL standard, see Section 1.7.2.3, “FOREIGN KEY Constraint Differences”. Referential ActionsWhen an UPDATE or DELETE operation affects a key value in the parent table that has matching rows in the child table, the result depends on the referential action specified by ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. Referential actions include:
For storage engines that support foreign keys, MySQL rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no matching candidate key value in the parent table. For an ON DELETE or ON UPDATE that is not specified, the default action is always NO ACTION. As the default, an ON DELETE NO ACTION or ON UPDATE NO ACTION clause that is specified explicitly does not appear in SHOW CREATE TABLE output or in tables dumped with mysqldump. RESTRICT, which is an equivalent non-default keyword, appears in SHOW CREATE TABLE output and in tables dumped with mysqldump. For NDB tables, ON UPDATE CASCADE is not supported where the reference is to the parent table's primary key. As of NDB 8.0.16: For NDB tables, ON DELETE CASCADE is not supported where the child table contains one or more columns of any of the TEXT or BLOB types. (Bug #89511, Bug #27484882) InnoDB performs cascading operations using a depth-first search algorithm on the records of the index that corresponds to the foreign key constraint. A foreign key constraint on a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE referential actions, nor can it use SET NULL or SET DEFAULT as ON DELETE referential actions. A foreign key constraint on the base column of a stored generated column cannot use CASCADE, SET NULL, or SET DEFAULT as ON UPDATE or ON DELETE referential actions. Foreign Key Constraint ExamplesThis simple example relates parent and child tables through a single-column foreign key: CREATE TABLE parent ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE child ( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ) ENGINE=INNODB;This is a more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customer table: CREATE TABLE product ( category INT NOT NULL, id INT NOT NULL, price DECIMAL, PRIMARY KEY(category, id) ) ENGINE=INNODB; CREATE TABLE customer ( id INT NOT NULL, PRIMARY KEY (id) ) ENGINE=INNODB; CREATE TABLE product_order ( no INT NOT NULL AUTO_INCREMENT, product_category INT NOT NULL, product_id INT NOT NULL, customer_id INT NOT NULL, PRIMARY KEY(no), INDEX (product_category, product_id), INDEX (customer_id), FOREIGN KEY (product_category, product_id) REFERENCES product(category, id) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (customer_id) REFERENCES customer(id) ) ENGINE=INNODB;Adding Foreign Key ConstraintsYou can add a foreign key constraint to an existing table using the following ALTER TABLE syntax: ALTER TABLE tbl_name ADD [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (col_name, ...) REFERENCES tbl_name (col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option]The foreign key can be self referential (referring to the same table). When you add a foreign key constraint to a table using ALTER TABLE, remember to first create an index on the column(s) referenced by the foreign key. Dropping Foreign Key ConstraintsYou can drop a foreign key constraint using the following ALTER TABLE syntax: ALTER TABLE tbl_name DROP FOREIGN KEY fk_symbol;If the FOREIGN KEY clause defined a CONSTRAINT name when you created the constraint, you can refer to that name to drop the foreign key constraint. Otherwise, a constraint name was generated internally, and you must use that value. To determine the foreign key constraint name, use SHOW CREATE TABLE: mysql> SHOW CREATE TABLE child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int DEFAULT NULL, `parent_id` int DEFAULT NULL, KEY `par_ind` (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci mysql> ALTER TABLE child DROP FOREIGN KEY `child_ibfk_1`;Adding and dropping a foreign key in the same ALTER TABLE statement is supported for ALTER TABLE ... ALGORITHM=INPLACE. It is not supported for ALTER TABLE ... ALGORITHM=COPY. Foreign Key ChecksIn MySQL, InnoDB and NDB tables support checking of foreign key constraints. Foreign key checking is controlled by the foreign_key_checks variable, which is enabled by default. Typically, you leave this variable enabled during normal operation to enforce referential integrity. The foreign_key_checks variable has the same effect on NDB tables as it does for InnoDB tables. The foreign_key_checks variable is dynamic and supports both global and session scopes. For information about using system variables, see Section 5.1.9, “Using System Variables”. Disabling foreign key checking is useful when:
When foreign_key_checks is disabled, foreign key constraints are ignored, with the following exceptions:
Disabling foreign_key_checks has these additional implications:
LockingMySQL extends metadata locks, as necessary, to tables that are related by a foreign key constraint. Extending metadata locks prevents conflicting DML and DDL operations from executing concurrently on related tables. This feature also enables updates to foreign key metadata when a parent table is modified. In earlier MySQL releases, foreign key metadata, which is owned by the child table, could not be updated safely. If a table is locked explicitly with LOCK TABLES, any tables related by a foreign key constraint are opened and locked implicitly. For foreign key checks, a shared read-only lock (LOCK TABLES READ) is taken on related tables. For cascading updates, a shared-nothing write lock (LOCK TABLES WRITE) is taken on related tables that are involved in the operation. Foreign Key Definitions and MetadataTo view a foreign key definition, use SHOW CREATE TABLE: mysql> SHOW CREATE TABLE child\G *************************** 1. row *************************** Table: child Create Table: CREATE TABLE `child` ( `id` int DEFAULT NULL, `parent_id` int DEFAULT NULL, KEY `par_ind` (`parent_id`), CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ciYou can obtain information about foreign keys from the INFORMATION_SCHEMA.KEY_COLUMN_USAGE table. An example of a query against this table is shown here: mysql> SELECT TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE WHERE REFERENCED_TABLE_SCHEMA IS NOT NULL; +--------------+------------+-------------+-----------------+ | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | CONSTRAINT_NAME | +--------------+------------+-------------+-----------------+ | test | child | parent_id | child_ibfk_1 | +--------------+------------+-------------+-----------------+You can obtain information specific to InnoDB foreign keys from the INNODB_FOREIGN and INNODB_FOREIGN_COLS tables. Example queries are show here: mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN \G *************************** 1. row *************************** ID: test/child_ibfk_1 FOR_NAME: test/child REF_NAME: test/parent N_COLS: 1 TYPE: 1 mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_FOREIGN_COLS \G *************************** 1. row *************************** ID: test/child_ibfk_1 FOR_COL_NAME: parent_id REF_COL_NAME: id POS: 0
Foreign Key ErrorsIn the event of a foreign key error involving InnoDB tables (usually Error 150 in the MySQL Server), information about the latest foreign key error can be obtained by checking SHOW ENGINE INNODB STATUS output. mysql> SHOW ENGINE INNODB STATUS\G ... ------------------------ LATEST FOREIGN KEY ERROR ------------------------ 2018-04-12 14:57:24 0x7f97a9c91700 Transaction: TRANSACTION 7717, ACTIVE 0 sec inserting mysql tables in use 1, locked 1 4 lock struct(s), heap size 1136, 3 row lock(s), undo log entries 3 MySQL thread id 8, OS thread handle 140289365317376, query id 14 localhost root update INSERT INTO child VALUES (NULL, 1), (NULL, 2), (NULL, 3), (NULL, 4), (NULL, 5), (NULL, 6) Foreign key constraint fails for table `test`.`child`: , CONSTRAINT `child_ibfk_1` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`) ON DELETE CASCADE ON UPDATE CASCADE Trying to add in child table, in index par_ind tuple: DATA TUPLE: 2 fields; 0: len 4; hex 80000003; asc ;; 1: len 4; hex 80000003; asc ;; But in parent table `test`.`parent`, in index PRIMARY, the closest match we can find is record: PHYSICAL RECORD: n_fields 3; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000001e19; asc ;; 2: len 7; hex 81000001110137; asc 7;; ...Warning If a user has table-level privileges for all parent tables, ER_NO_REFERENCED_ROW_2 and ER_ROW_IS_REFERENCED_2 error messages for foreign key operations expose information about parent tables. If a user does not have table-level privileges for all parent tables, more generic error messages are displayed instead (ER_NO_REFERENCED_ROW and ER_ROW_IS_REFERENCED). An exception is that, for stored programs defined to execute with DEFINER privileges, the user against which privileges are assessed is the user in the program DEFINER clause, not the invoking user. If that user has table-level parent table privileges, parent table information is still displayed. In this case, it is the responsibility of the stored program creator to hide the information by including appropriate condition handlers. Can foreign key references foreign key?A foreign key can reference any field defined as unique. If that unique field is itself defined as a foreign key, it makes no difference. A foreign key is just to enforce referential integrity.
Does foreign key have to reference primary key in another table?Creating a foreign key is almost as easy as creating a primary key, except that SQL Server imposes several more rules on foreign keys. For example, the foreign key must reference a primary key or unique constraint, although that reference can be on the same table or on a different table.
Can a foreign key reference the same table?FOREIGN KEY constraints can reference another column in the same table, and is referred to as a self-reference. A FOREIGN KEY constraint specified at the column level can list only one reference column. This column must have the same data type as the column on which the constraint is defined.
Why foreign key is called referential integrity?Referential integrity refers to the relationship between tables. Because each table in a database must have a primary key, this primary key can appear in other tables because of its relationship to data within those tables. When a primary key from one table appears in another table, it is called a foreign key .
|