How to create and query foreign keys in mysql

1. What is a foreign key

A foreign key is one or more columns in a table. It is used to specify the values ​​in this column or multiple columns. The value must match data in another table or a primary key in another table. This matched table is called the referenced table. A foreign key is a mechanism in a relational database used to join two or more tables.

The purpose of foreign keys is to ensure data integrity and consistency. When we add a row of data to a table, if this data needs to match some data in another table, if the matching relationship is established through a foreign key, then MySQL will check the foreign key in the newly added data. , to see if the data exists in the referenced table. If it does not exist, it will refuse to insert the data in the table.

2. How to create foreign keys

There are many ways to create foreign keys in MySQL. The following are two common ways.

1. Use the ALTER TABLE statement

The syntax format for adding foreign keys through the ALTER TABLE statement is as follows:

ALTER TABLE table name ADD CONSTRAINT foreign key name FOREIGN KEY (column name ) REFERENCES The table name (column name) of another table;

where the foreign key name is a name given to the foreign key. This name should be unique, and the column name refers to the column name of the current table. The indication and column name of another table are the name of the referenced table and the column name of the referenced table respectively.

For example, we have an employee table (employees) and a department table (departments), which are linked through a foreign key. We can use the following SQL statement to create this foreign key.

ALTER TABLE employees ADD CONSTRAINT dept_fk FOREIGN KEY (dept_id) REFERENCES departments (dept_id);

This statement means to add a foreign key named dept_fk to the employees table (employees). dept_fk points to the dept_id column of the employees table, which refers to the dept_id column of the departments table.

2. Define foreign keys when creating a table

Defining foreign keys when creating a table is also a common way. The syntax format is as follows:

CREATE TABLE 表名 (
列名 数据类型,
外键名称 FOREIGN KEY (列名) REFERENCES 另一个表格的表名 (列名)
);

For example, to create an employee table (employees) and a department table (departments), and use the dept_id column as the foreign key of the employees table to link the dept_id column of the departments table, you can use the following SQL statement.

CREATE TABLE employees (
id INT NOT NULL,
name VARCHAR(50) NOT NULL,
dept_id INT,
PRIMARY KEY (id),
FOREIGN KEY (dept_id) REFERENCES departments (dept_id)
)

3. How to query foreign keys

Once foreign keys are established in MySQL, you can use MySQL query statements to query them. The syntax format of querying foreign keys is as follows:

SELECT * FROM 表名 WHERE 列名 IS NULL;

This statement returns data with empty column names in the table. If this column is defined as a foreign key, then the query result is the data in the table with the foreign key There is no matching row of data. The results of this query are very useful in helping us determine which records were not matched correctly and need to be manually resolved.

For example, we have a departments table, and its dept_id column is defined as a foreign key. If we want to query for records that do not match the departments table, we can use the following SQL statement:

SELECT * FROM departments WHERE dept_id NOT IN (SELECT dept_id FROM employees);

This statement means to query the dept_id data in the departments table that is not included in the employees table.

The above is the detailed content of How to create and query foreign keys in mysql. For more information, please follow other related articles on the PHP Chinese website!