MySQL foreign key setting method example

1. Foreign key setting method

1. In MySQL, in order to associate two tables, two important functions are used: foreign key (FOREIGN KEY) and connection (JOIN). Foreign keys need to be defined when creating a table. Joins can connect two tables through fields with the same meaning and are used in the query stage.

2. Suppose there are two tables, Table A and Table B, which are associated through a common field id. We call this association R. If id is the primary key in table A, then table A is the primary table in this relationship R. Correspondingly, table B is the secondary table in this relationship. The id in table B is what table B uses to reference the data in table A, which is called a foreign key. So, the foreign key is the common field in the slave table that is used to reference data in the master table.

Create the main table

CREATE TABLE demo.importhead ( 
listnumber INT PRIMARY KEY, 
supplierid INT, 
stocknumber INT, 
importtype INT, 
importquantity DECIMAL(10 , 3 ), 
importvalue DECIMAL(10 , 2 ), 
recorder INT, 
recordingdate DATETIME);

Create a secondary table

CREATE TABLE demo.importdetails( 
listnumber INT, 
itemnumber INT, 
quantity DECIMAL(10,3), 
importprice DECIMAL(10,2), 
importvalue DECIMAL(10,2), -- Define foreign key constraints, specify the foreign key field and the referenced primary table field CONSTRAINT fk_importdetails_importhead 
FOREIGN KEY (listnumber) REFERENCES importhead (listnumber)
);

By running this SQL statement, we define a foreign key constraint named fk_importdetails_importhead while creating the table. At the same time, we declare that the field listnumber of this foreign key constraint refers to the field listnumber in the table importhead.

After the creation is complete, we can view it through SQL statements. Here we need to use the MySQL built-in database for storing system information:

information_schema. We can view relevant information about foreign key constraints:

The table where the foreign key constraint is located is importdetails, and the foreign key field is listnumber

The referenced main table is importhead, and the referenced main table field is listnumber.

In this way, by defining the foreign key constraint, we have established an association relationship between the two tables.

3. Connect

There are two types of joins in MySQL, INNER JOIN and OUTER JOIN.

• Inner join means that the query results only return records that meet the join conditions. This join method is more commonly used.
• Outer joins are different, meaning that the query results return all records in a certain table and the records in another table that meet the join conditions.

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

• The primary 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 primary table.
• Primary keys 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 content of this foreign key is correct.
• Specify a column name or 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 primary table.
• The data type of the foreign key column must be the same as the data type of the corresponding column in the primary key of the primary table.

Summarize

This is the end of this article about MySQL foreign key settings. For more relevant MySQL foreign key settings, please search for previous articles on 123WORDPRESS.COM or continue to browse the following related articles. I hope everyone will support 123WORDPRESS.COM in the future!