Add a foreign key to the mysql DATA table (1)

1. What is referential integrity? Integrity of reference (integrity constraints) is an important concept in database design. When a table in the database is associated with one or more tables, the integrity of reference is involved. For example, the following example shows the document category table.

1. What is referential integrity? Integrity of reference (integrity constraints) is an important concept in database design. When a table in the database is associated with one or more tables, the integrity of reference is involved. For example, in the following example: Classification Table-categories category_id name 1 SQL Server 2 Oracle 3 PostgreSQL 5 sqls

1. What is referential integrity?
-------
Integrity of reference (integrity constraints) is an important concept in database design. When a table in the database is associated with one or more tables, the integrity of reference is involved. For example:
Document category table-categories
Category_id name
1. SQL Server
2 Oracle
3 PostgreSQL
5 SQLite

Article table-articles
Article_id category_id title
1 1 aa
2 2 bb
3 4 cc

The preceding two tables use category_id. The categories table has four records and the articles table has three records.

However, for some reason, we may have deleted the records whose category_id is 4 in the categories table, but the articles table still has a record whose category_id is 4. Obviously, this record with category_id = 4 should not exist in the articles table, which can easily cause data disorder.

On the contrary, the Foreign Key relationship (Foreign Key relationships) discusses the relationship between the parent table (categories) and the sub-table (articles) by introducing the Foreign Key (Foreign Key) this concept ensures Referential integrity, which makes the database quite simple. For example, to delete the category_id = 4 record in the categories table and delete all records in the category_id = 4 in the articles table, if the foreign key is not introduced, we must execute two SQL statements; if there is a foreign key, you can easily use an SQL statement to meet the requirements.

2. Conditions for using foreign keys
------
MySQL only introduces Foreign keys after v3.23.34, so do not think about the previous versions :). In addition, you must meet the following conditions:

1) Open InnoDB Engine Support in my. cnf configuration file.
# Uncomment the following if you are using InnoDB tables
Innodb_data_home_dir =/var/db/mysql/
Innodb_data_file_path = ibdata1: 10 M: autoextend
Innodb_log_group_home_dir =/var/db/mysql/
Innodb_log_arch_dir =/var/db/mysql/

2) All associated tables must use the InnoDB engine.

3) All associated fields must be created.
MySQL v4.0 and later versions are automatically created when defining foreign keys. Therefore, you must manually define indexes before v4.0 (including v4.0.

4) The associated fields must adopt similar data types or convertible data types. Of course, it is best to use the same types.
For example, if the field in the parent table is of the TINYINT type, the child table can only use TINYINT, SMALLINT, INT, BIGINT, and other types.

3. Foreign key syntax reference
-----
You can use create table or alter table to define foreign keys.
Create table Syntax:
CREATE [TEMPORARY] TABLE [if not exists] tbl_name
[(Create_definition,…)]

Create_definition:
Column_definition
| [CONSTRAINT [symbol] foreign key [index_name] (index_col_name ,...) [Reference_definition]

Column_definition:
Col_name type [not null | NULL] [DEFAULT default_value]
[AUTO_INCREMENT] [UNIQUE [KEY] | [PRIMARY] KEY]
[COMMENT 'string'] [reference_definition]

Index_col_name:
Col_name [(length)] [ASC | DESC]

Reference_definition:
REFERENCES tbl_name [(index_col_name,…)]
[Match full | match partial | match simple]
[On delete reference_option]
[On update reference_option]

Reference_option:
RESTRICT | CASCADE | set null | NO ACTION

Alter table Syntax:
ALTER [IGNORE] TABLE tbl_name
Alter_specification [, alter_specification]…

Alter_specification:
| ADD [CONSTRAINT [symbol] primary key [index_type] (index_col_name ,...)
| ADD [CONSTRAINT [symbol] UNIQUE [INDEX] [index_name] [index_type] (index_col_name ,...)
| ADD [CONSTRAINT [symbol] foreign key [index_name] (index_col_name ,...) [Reference_definition]
| Drop foreign key fk_symbol

4. Define Foreign keys
----
Mysql>Create table categories (
-> Category_id tinyint (3) unsigned not null AUTO_INCREMENT,
-> Name varchar (30) not null,
-> Primary key (category_id)
->) ENGINE = INNODB;
Query OK, 0 rows affected (0.36 sec)

Mysql>Insert into categories VALUES (1, 'SQL Server'), (2, 'oracle'), (3, 'postgresql '), (4, 'mysql'), (5, 'sqlite ');
Query OK, 5 rows affected (0.48 sec)
Records: 5 Duplicates: 0 Warnings: 0

Mysql>Create table members (
-> Member_id INT (11) unsigned not null,
-> Name VARCHAR (20) not null,
-> Primary key (member_id)
->) ENGINE = INNODB;
Query OK, 0 rows affected (0.55 sec)

Mysql>Insert into members VALUES (1, 'test'), (2, 'admin ');
Query OK, 2 rows affected (0.44 sec)
Records: 2 Duplicates: 0 Warnings: 0

Mysql>Create table articles (
-> Article_id INT (11) unsigned not null AUTO_INCREMENT,
-& Gt; title varchar (255) not null,
-> Category_id tinyint (3) unsigned not null,
-> Member_id int (11) unsigned not null,
-> INDEX (category_id ),
-> Foreign key (category_id) REFERENCES categories (category_id ),
-> CONSTRAINT fk_member foreign key (member_id) REFERENCES members (member_id ),
-> Primary key (article_id)
->) ENGINE = INNODB;
Query OK, 0 rows affected (0.63 sec)

Note: For non-InnoDB tables, the foreign key clause is ignored.
If the following error occurs:
ERROR 1005: Can't create table './test/articles. frm' (errno: 150)
Check the following definition statements carefully. Common Errors are that the table type is not INNODB, related fields are written incorrectly, and indexes are missing.

So far categories. category_id and articles. category_id and members. member_id and articles. member_id has a foreign key relationship, only articles. the value of category_id exists with categories. in the category_id table, and articles. the value of member_id exists with members. in the member_id table, data can be inserted or modified. For example:

Mysql>Insert into articles (category_id, member_id, title) VALUES (6, 1, 'foo ');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('test/articles ', CONSTRAINT 'articles _ ibfk_1 'foreign key ('category _ id') REFERENCES 'category' ('id '))

Mysql>Insert into articles (category_id, member_id, title) VALUES (3, 3, 'foo ');
ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails ('test/articles ', CONSTRAINT 'fk _ member' foreign key ('Member _ id') REFERENCES 'members '('Member _ id '))

It can be seen that the above two statements will have errors, because in the categories table there is no category_id = 6, members table also does not have a member_id = 3 record, so you cannot insert. The following SQL statement can be used.

Mysql>Insert into articles (category_id, member_id, title) VALUES (3, 2, 'bar ');
Query OK, 1 row affected (0.03 sec)

5. Delete foreign key definitions
-----
I wonder if you have found that the foreign key articles. member_id has one more CONSTRAINT fk_member than the articles. category_id clause when defining a foreign key?
This fk_member is used to delete the foreign key definition, as shown below:
Mysql>Alter table articles drop foreign key fk_member;
Query OK, 1 row affected (0.25 sec)
Records: 1 Duplicates: 0 Warnings: 0

In this way, the foreign key definition of articles. member_id is deleted, but how can I delete it if the CONSTRAINT fk_symbol (that is, the foreign key symbol) is not specified during the definition? Don't worry. MySQL will create one by itself, but you can run the following command to view it:

Mysql>Show create table articles;
+ ---- + ------------ +
| Table | Create Table |
+ ---- + ------------ +
| Articles | create table 'articles '(
'Article _ id' int (11) unsigned not null auto_increment,
'Category _ id' tinyint (3) unsigned not null,
'Member _ id' int (11) unsigned not null,
'Title' varchar (255) not null,
Primary key ('Article _ id '),
KEY 'category _ id' ('category _ id '),
KEY 'Member _ id' ('Member _ id '),
CONSTRAINT 'articles _ ibfk_1 'foreign key ('category _ id') REFERENCES 'category' ('id ')
) ENGINE = InnoDB default charset = latin1 |
+ ---- + ------------ +
1 row in set (0.01 sec)

We can see that the foreign key symbol of articles. category_id is articles_ibfk_1, because you can run the following command to delete the foreign key definition:

Mysql>Alter table articles drop foreign key articles_ibfk_1;
Query OK, 1 row affected (0.66 sec)
Records: 1 Duplicates: 0 Warnings: 0

6. Summary
---
The disadvantage of the introduction of foreign keys is that the speed and performance will decrease. Of course, foreign keys have many advantages. This article only discusses how to define and delete Foreign keys. The practical application of foreign keys will be introduced in future articles.