mysql query foreign key

MySQL is a relational database management system, and foreign keys are one of the important concepts of relational databases. Foreign key is a constraint used to describe the relationship between tables, which can ensure the integrity and consistency of data. Querying foreign keys in MySQL is also a very important operation. This article will introduce how to use MySQL to query foreign keys.

1. What is a foreign key

A foreign key is one or more columns in a table. It is used to specify that the value in this column or columns must be consistent with the data in another table. Or match the primary key in another table. This matched table is called the referenced table. In a relational database, we can use foreign keys when we want to link 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

After the foreign key is established in MySQL, we can use the MySQL query statement to query the foreign key. 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. This query result is very useful, it can help us find those records that need to be solved manually and are not matched correctly.

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.

4. Summary

MySQL querying foreign keys is a very important operation. Through the definition and query of foreign keys, we can ensure the integrity and consistency of data in the database, prevent unnecessary errors, and make the data more accurate and reliable. When using MySQL, pay attention to the definition and query of foreign keys to avoid unnecessary errors due to foreign key constraints.

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