Learning video: https://www.bilibili.com/video/BV1tJ411r7EC?p=35

Data integrity: ensure that the data inserted into the table must be correct. Including entity integrity, region integrity, reference integrity and custom integrity.

Constraints: constraints are used to ensure the integrity of data, including primary key constraints (PK), unique constraints (), check constraints, and foreign key constraints. The difference between a primary key and a unique constraint is that the primary key cannot be empty. Naming rule: constraint type constraint name.

Column level constraint: constraint set for a column. Understanding of the difference between table level and column level constraints: https://blog.csdn.net/hanxuemin12345/article/details/7828206

--Simple way to set primary key
create table user1(
id number(5) primary key,
name varchar2(20)
)

--Set the complete write method of primary key
create table user1(
id number(5) constraint pk_id primary key,
name varchar2(20)
)

--Add primary key constraint, unique constraint, non empty constraint
create table user3(
id number(3) constraint pk_user3_id primary key,
name varchar2(20) constraint uq_user3_name unique,
age number(3) constraint nn_user3_age not null
)

insert into user3 values(1,'bibi',34);
insert into user3 values(2,'bibiiii',34);
insert into user3 values(3,'bibi',null);

Check constraints:

alter table user3 add salary number(8,2);
alter table user3 add constraint ck_user3_salary check (salary between 5000 and 20000);

Table level constraint: after defining some columns of the table, add constraints to some columns. Non empty constraints can only be column level constraints.

create table user3(
id number(3) ,
name varchar2(20) ,
age number(3) not null,
constraint pk_user3_id primary key (id),
constraint uq_user3_name unique (name)
)

create table user3(
id number(3) ,
name varchar2(20) ,
age number(3) not null,
constraint uq_user3_name unique (id,name)
)

After table definition, add and remove constraints:

alter table user3 add constraint pk_usesr3_id primary key (id);
alter table user3 drop constraint pk_user3_id;

Foreign key constraint: the value of this column in this table depends on the primary key of another table. To delete the associated data of the primary table, you must first delete or empty the associated data of the foreign key from the table. Before deleting the primary table, you must first delete or disconnect all associated foreign keys from the primary table.

create table user8
(
id number(3) primary key,
name varchar2(20) not null
);

insert into user8 values(1,'bb');
insert into user8 values(2,'bbb');
insert into user8 values(3,'bbbb');

create table order8
(
 id number(10)  primary key,
 user_id number(3) constraint fk_order8_userid references user8(id), --Foreign key, referencing user8 Table id
 info varchar2(500) 
)；

insert into order8 values(1,1,null);
insert into order8 values(1,5,null);

create table user8
(
id number(3) primary key,
name varchar2(20) not null
);

insert into user8 values(1,'bb');
insert into user8 values(2,'bbb');
insert into user8 values(3,'bbbb');

create table order8
(
 id number(10)  primary key,
 user_id number(3) constraint fk_order8_userid references user8(id), --Foreign key, referencing user8 Table id
 info varchar2(500) 
)

insert into order8 values(1,1,null);
insert into order8 values(2,2,null);
insert into order8 values(3,3,null);

--Delete associated data method 1, not commonly used
delete from order8;
delete from user8;

--Method two empty
update order8 set user_id=null;
delete from user8;

--Deleting association tables is not commonly used
drop table order8;
drop table user8;

--Method 2 delete all associated foreign keys
drop table user8 cascade constraint;

Cascade operation: when the referenced data in the main table is deleted, the associated foreign key field data from the table is automatically set to null, and the usage is on delete set null. When deleting the referenced data of the main table, the data row associated with the foreign key of the table will be deleted automatically. Use on delete cascade.

create table order8
(
 id number(10)  primary key,
 user_id number(3) references user8(id) on delete set null, 
 info varchar2(500) 
)

create table order8
(
 id number(10)  primary key,
 user_id number(3) references user8(id) on delete cascade, 
 info varchar2(500) 
)

Union primary key: multiple fields form a primary key

create table user8
(
id number(3) ,
name varchar2(20),
constraint pk_user8_id_name primary key (id,name)
);

insert into user8 values(1,'b');
insert into user8 values(1,'a');
insert into user8 values(1,'b');