Management of constraints

Common constraints

A restriction used to restrict the data in a table in order to ensure the accuracy and reliability of the data in the table

classification

• Not null: non null, used to ensure that the value of this field cannot be null
• Default: default to ensure that this field has a default value
• Primary key: primary key, used to ensure that the value of this field is unique and not empty
• Unique: unique. It is used to ensure that the value of this field is unique. It can be blank
• Check: check constraints (not supported by mysql)
• Foreign key: foreign key. It is used to restrict the relationship between two tables. It is used to ensure that the value of this field must come from the value of the associated column of the main table. When adding a foreign key constraint from the table, it is used to reference the value of a column in the main table.

Foreign key characteristics

• Setting foreign key relationships from tables
• The type of the foreign key column of the slave table and the type of the associated column of the master table are required to be consistent or compatible
• The associated column of the main table must be a key (primary key or unique)
• When inserting data, insert the master table first and then the slave table
• When deleting data, delete the secondary table first, and then the primary table

Add classification of constraints

• Column level constraint

  Both are supported, but foreign key constraints have no effect

• Table level constraints

  Except that it is not empty, it is supported by default

When to add constraints

When creating a table

Add column level constraints

Add constraint type directly after field name and type

Create table stuinfo(
Id int primary key,
stuName varchar(20) not null,
Gender char(1) check(gender='male' or gender = 'female'),#Check, not supported
Seat int unique,
Age int default 18,
Majorid int foreign key references major(id)#Foreign key, no effect
);

Create table major(
Id int primary key,
Majorname varchar(20)
);

Add table level constraints

grammar

[Constraint] Constraint name constraint type(Listing);

Create table stuinfo1(
Id int,
stuName varchar(20),
Gender char(1),
Seat int,
Age int,
Majorid int,
Constraint pk primary key(id),
Constraint uq unique(seat),
Constraint fk_stuinfo_major foreign key(majorid) references major(id) 
);

When modifying a table

Add column level constraints

Alter table Table name modify column Field name field type new constraint;

Add table level constraints

Alter table Table name add [constraint Constraint name] constraint type(Field name) [references surface(field)];

#Add a non empty constraint
Alter table stuinfo modify column stuname varchar(20) not null;
#Add default constraint
Alter table stuinfo modify column age int default 18;
#Add primary key
	#Column level constraint
Alter table stuinfo modify column id int primary key;
	#Table level constraints
alter table stuinfo add primary key(id);
#Add unique constraint
	#Column level constraint
Alter table stuinfo modify column seat int unique;
	#Table level constraints
Alter table stuinfo add unique(seat);
#Add foreign key
Alter table stuinfo add constraint fk_stuinfo_major foreign key(majorid) references major(id);

Delete constraint

Delete primary key, unique key and foreign key
alter table name drop constraint type [constraint name];
Delete non empty, default
alter table name modify column name;

#Delete non empty
Alter table stuinfo modify column stuname varchar(20) null;
#Delete default
Alter table stuinfo modify column age int;
#Delete primary key
Alter table stuinfo drop primary key;
#Delete unique
Alter table stuinfo drop index seat;
#Delete foreign key
Alter table stuinfo drop foreign key majorid;

Identity column

Also known as self growing column
Instead of manually inserting values, the system provides default sequence values

characteristic

• The identification column is not necessarily matched with the primary key, but must be a key
• A table can have at most one identity column
• The type of identity column can only be numeric
• The identification column can set the step size

Set auto_increment_increment = step;

• You can manually insert a value to set the starting value

set auto_increment_offset = start;

Set identity column when creating table

Create table tab(
Id int primary key auto_increment,
Name varchar(20)
);

Show variables like '%auto_increment%';

Set identity column when modifying table

Alter table tab modify column id int primary key auto_increment;

Delete identity column when modifying table

Alter table tab modify column id int primary key;