How to add foreign key constraints in mysql

The "ALTER TABLE" statement and the "FOREIGN KEY" keyword can be used to add foreign keys in mysql. The syntax 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.

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

• The main table must already exist in the database, or be 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 primary 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.

mysql adds foreign key constraints to the table

Foreign key constraints can be added when modifying the table, but adding foreign key constraints The premise of key constraints 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 syntax format for adding foreign key constraints when modifying the data table is as follows:

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

Example

Modify the data table tb_emp2 and set the field deptId to Foreign key, associated with the primary key id of the data table tb_dept1

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 column cannot be empty.

[Related recommendations: mysql video tutorial]

The above is the detailed content of How to add foreign key constraints in mysql. For more information, please follow other related articles on the PHP Chinese website!