For one more

Implemented by foreign keysCopy the code

Class Table (CALSS)

cid caption
1 Three years class two
2 Class 3 year
3 Three years class

Students table (student)

sid sname gender class_id
1 Steel eggs female 1
2 The hammer male 1
1 Mountain artillery male 2

For the above two tables, the class and students constitute a one-to-many relationship, a class can have more than one student, SQL implementation just add class_id foreign key for the student table can be realized

create table class(
    cid int not null auto_increment primary key,
    caption varchar(10))create table student(
    sid int not null auto_increment primary key,
    sname varchar(10),
    gender  enum('male'.'woman') not null default 'male',
    class_id int.constraint fk_s_c foreign key student(class_id) references class(cid)
)
Copy the code

One to one

By foreign key + unique index implementation, one-to-one is actually a many-to-one basis to add a unique indexCopy the code
sid sname gender class_id
1 Steel eggs female 1
2 The hammer male 1
1 Mountain artillery male 2

As shown in the table above, the steel egg and hammer class_id are the same (belonging to the same class). If we set class_id as the unique index, then the steel egg and hammer class_id cannot be the same, that is, all students must belong to different classes. This is one-to-one, and the SQL is as follows

create table student(
    sid int not null auto_increment primary key,
    sname varchar(10),
    gender  enum('male'.'woman') not null default 'male',
    class_id int.constraint fk_s_c foreign key student(class_id) references class(cid),
    constraint uq_name unique (class_id)
)
Copy the code

Many to many

Implemented by associated tablesCopy the code

The order sheet

id price
1 100.99
2 100.00

Goods table

id name
1 shirt
2 A pair of jeans 
For example, there are two shirts and one pair of pants in order 1. Many-to-many can be realized through two foreign keysCopy the code

Order details mark

id oid pid
1 1 1
2 1 1
3 1 2
4 2 1
5 2 2

Implement SQL as follows

-- I used the order keyword for the first time, but I didn't know what was wrong after searching for a long time
create table order_info(
    id int not null auto_increment primary key,
    price decimal(5.2) not null
)
create table product(
    id int not null auto_increment primary key,
    name varchar(50) not null
)

create table order_product(
    id int not null auto_increment primary key,
    oid int,
    pid int.constraint fk_o_p_o foreign key (oid) references order_info(id),
    constraint fk_o_p_p foreign key (pid) references product(id)
)
Copy the code

If we want to realize the same order, there can be no more than one item, then we can add a unique(OID, PID). The specific need to add depends on the actual business requirements

create table order_product(
    id int not null auto_increment primary key,
    oid int,
    pid int.constraint fk_o_p_o foreign key (oid) references order_info(id),
    constraint fk_o_p_p foreign key (pid) references product(id),
    unique (oid,pid)
)
Copy the code