What is the SQL statement to create a table foreign key constraint?

SQL foreign key constraints can be specified through the FOREIGN KEY keyword. The creation statement is "ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (column name) REFERENCES main table name (column name);".

The operating environment of this tutorial: windows7 system, mysql8 version, Dell G3 computer.

MySQL foreign key constraint (FOREIGN KEY) is a special field of the table, often used together with primary key constraints. For two tables with an associated relationship, the table where the primary key in the associated field is located is the primary table (parent table), and the table where the foreign key is located is the secondary table (child table).

Foreign keys are used to establish the association between the master table and the slave table, establish a connection for the data in the two tables, and constrain the consistency and integrity of the data in the two tables.

Create table foreign key constraints

You can specify foreign keys through the FOREIGN KEY keyword in the ALTER TABLE statement; add foreign key constraints The premise is: the data in the foreign key column in the secondary table must be consistent with the data in the primary key column in the primary table or there is no data.

The specific syntax format is as follows:

ALTER TABLE <数据表名> ADD CONSTRAINT <外键名>
FOREIGN KEY(<列名>) REFERENCES <主表名> (<列名>);

When defining foreign keys, you need to comply with the following rules

• The main table must have Exists in the database, or is the table currently being created. If it is the latter case, the master table and the slave table are the same table. Such a table is called a self-referential table, and this structure is called self-referential integrity.

• A primary key must be defined for the main table.

• The primary key cannot contain null values, but null values ​​are allowed in foreign keys. That is, as long as every non-null value of the foreign key appears in the specified primary key, the contents of the foreign key are correct.

• Specify the column name or a combination of column names after the table name of the main table. This column or combination of columns must be the primary key or candidate key of the main table.

• The number of columns in the foreign key must be the same as the number of columns in the primary key of the main table.

• The data type of the column in the foreign key must be the same as the data type of the corresponding column in the primary key of the main table.

Example:

Modify the data table tb_emp2, set the field deptId as a foreign key, and compare it with the primary key id of the data table tb_dept1 The correlation, SQL statement and running results are shown below.

mysql> ALTER TABLE tb_emp2
    -> ADD CONSTRAINT fk_tb_dept1
    -> FOREIGN KEY(deptId)
    -> REFERENCES tb_dept1(id);
Query OK, 0 rows affected (1.38 sec)
Records: 0  Duplicates: 0  Warnings: 0
mysql> SHOW CREATE TABLE tb_emp2\G
*************************** 1. row ***************************
       Table: tb_emp2
Create Table: CREATE TABLE `tb_emp2` (
  `id` int(11) NOT NULL,
  `name` varchar(30) DEFAULT NULL,
  `deptId` int(11) DEFAULT NULL,
  `salary` float DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `fk_tb_dept1` (`deptId`),
  CONSTRAINT `fk_tb_dept1` FOREIGN KEY (`deptId`) REFERENCES `tb_dept1` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=gb2312
1 row in set (0.12 sec)

Note: When adding foreign key constraints to the created data table, make sure that the values ​​of the columns to which the foreign key constraints are added all come from the primary key columns, and the foreign key columns cannot be empty.

(Recommended tutorial: mysql video tutorial)

The above is the detailed content of What is the SQL statement to create a table foreign key constraint?. For more information, please follow other related articles on the PHP Chinese website!