summary

This article mainly uses the Navicat for MySQL tool to analyze the common constraints of the SQL language through programming and code testing: primary key constraints   And foreign key constraints.

Each table in the business needs to have one and only one primary key.

There can be multiple foreign keys in a table, but after adding foreign key constraints, fields add foreign key constraints to the table as a child table, foreign keys to the child table refer to fields from the parent table's primary key field or uniqueness constraints, referenced tables act as parent tables, and there are details between the parent and child tables that need attention.

In this paper, we try to help readers understand primary and foreign keys thoroughly through code testing combined with conceptual analysis.

1. Create parent table before child table

Create the primary table to reference first (where the foreign key comes from)

Create a sub-table and execute it sequentially or you will get an error

• 🔲 Correct table building statement:

drop table if exists stu;
drop table if exists class;
create table stu
(
	sno int PRIMARY key,
	sname VARCHAR(10),
	major VARCHAR(10),
	cno int foreign key references class(cno)
);

create table class
(
	cno int primary,
	cname VARCHAR(5)
);

2. Delete child table before parent table when deleting

DROP TABLE IF EXISTS class;
DROP table if exists stu;

CREATE TABLE class
(
	cno int primary key,
	cname VARCHAR(10)
);

create table stu
(
	sno int primary key,
	sname VARCHAR(5),
	cno int,
	FOREIGN key(cno) REFERENCES class(cno)
); 

Otherwise an error will be made:

Correct deletion method:

3. Primary key: non-empty and unique

Primary keys can have only one table, either table-level or column-level constraints.

The primary key is the combination of the non-null and unique constraints

Table level constraints are two or more fields joined together to meet the above requirements!

Definition statement for primary key

sno int primary key;//Column-level constraint, which constrains the field (column, columu) of the school number, requires that it is not empty and cannot be repeated!
primary key(sno);//A field can also use table-level constraints like this, but it doesn't make much sense
primary key(name,id);//Connect name and ID number as primary key

Although multiple fields are supported to be combined as a union primary key.

But by the way, this is not recommended in real business

The first paradigm requires that non-primary keys cannot be partially dependent on primary keys. Compound Primary Key cracks the first paradigm perfectly!

There are two common primary keys:

1. Business Primary Key Business Primary Key is business related, data reuse efficiency is low, business changes are too complex, and business related.

2. Natural Primary Key recommends using Natural Primary Key. Natural Primary Key has nothing to do with business and plays by itself. Business changes do not affect the use of data.

Natural primary keys are very common among them, and when it comes to natural primary keys, you have to mention that the values of primary keys increase by themselves.

For example, when we insert data, we can insert a data regardless of (natural primary key), and the primary key value increases automatically.

insert into class
(cname)
VALUES
('jd'),('jz'),('gk');

select * from class;


insert into stu
(sname,cno)
VALUES
('cwq',1),('ljb',2),('cyc',3);

select * from stu;

The result of executing the above statement is as follows:

The reason is that we added this sentence when defining the primary key value:

cno int primary key auto_increment;

auto_increment, primary key value increases by itself

4. Foreign keys: primary keys or unique values associated with other tables

Constraints on foreign keys

First, foreign keys are fields in this table, this table is a child table, and usually foreign keys refer to the primary key of the parent table

But when a foreign key refers to a field whose parent table is not the primary key

At this point, a column constrained by a foreign key can have one or more nulls at the same time because nulls are not values and there are no duplicates.

That is, the non-empty constraint can be satisfied, but the uniqueness constraint of the uniqueness can still be satisfied, and there can be no duplication

Foreign keys: In short, the primary key or unique value associated with another table

Declarative syntax for foreign keys:

Foreign key (field name) references primary table table table name (field name of column)

foreign key(Field name) references   Main Table Table Table Name(Field name of column)

You cannot insert elements that do not exist in the corresponding column of the parent table into the column of the foreign key of the child table!

[ SQL ]


Insert into stu (cno)value(7)in;

[ Err ]1452-Cannot add or update subrows: Foreign key constraint failed(' srs'). ' stu'，CONSTRAINT' stu _ ibfk _ 1' FOREIGN KEY (' cno') REFERENCES' class'(' cno'))




[SQL]

insert into stu(cno) values(7);
[Err] 1452 - Cannot add or update a child row: a foreign key constraint fails (`srs`.`stu`, CONSTRAINT `stu_ibfk_1` FOREIGN KEY (`cno`) REFERENCES `class` (`cno`))

There is no limit to the number of foreign keys in a table, but the columns in a child table refer to the corresponding columns in the parent table, and the corresponding column constraints should be primary key or unique constraints

Solution: Add unique constraints because the fields associated with other tables by foreign keys need to be primary keys or at least satisfy unique constraints;

drop table if exists stu;
 drop table if exists class;

create table class
(
	cno int primary key auto_increment,
	cname VARCHAR(5) unique
);

create table stu
(
	sno int PRIMARY key auto_increment,
	sname VARCHAR(10),
	major VARCHAR(10),
	cno int ,
	cname VARCHAR(5),
	FOREIGN key (cno) references class(cno),
	foreign key (cname) references class(cname)
);

insert into class
(cname)
VALUES
('jd'),('jz'),('gk'),('gygc');

select * from class;


insert into stu
(sname,cno)
VALUES
('cwq',1),('ljb',2),('cyc',3);



insert into stu (cno) VALUES ('4');
select * from stu;