MySQL common constraint analysis

MySQL constraint is a restriction used to restrict the data in the table in order to ensure the accuracy and reliability of the data in the table.

classification

Six constraints:

  •   NOT NULL: non null, used to ensure that the value of this field cannot be null. For example, the student name and student number in the student table.
  •   DEFAULT: DEFAULT value, used to ensure that the field has a DEFAULT value. For example, the student gender of the student table
  •   PRIMARY KEY: PRIMARY KEY, used to ensure that the value of this field is unique and non empty. For example, the student number in the student table.
  •   UNIQUE: UNIQUE. It is used to ensure that the value of this field is UNIQUE. It can be blank. For example, the mobile phone number of the registered user, the ID number, etc.
  •   CHECK: CHECK the constraint (not supported by MySql) and CHECK whether the value of the field is the specified value.
  •   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 primary table. When adding a FOREIGN KEY constraint from the secondary table, it is used to reference some values in the primary table. For example, the major number of the student table

When to add constraints

  •   When creating a table
  •   When modifying a table

Add classification of constraints

  •   Column level constraints: the six constraints are syntactically supported, but foreign key constraints have no effect
  •   Table level constraints: supported except non empty, default, and others.

Add column level constraints

create table if not exists t_stuinfo(
    id int primary key,                         #Primary key
    stuName varchar(20) not null,                #Non empty
    gender char(1) check(gender='male' or gender='female'),    #Check the constraints. MySql has no effect, but no error is reported
    seat int unique,                        #Unique constraint
    age int default 18,                      #Default (value) constraint
    majorId int references major(id)            #For foreign key constraints, MySql has no effect, but no error is reported
);

Add table level constraints

Syntax: at the bottom of the create table field

constraint Constraint name constraint type(Field name)
create table if not exists t_stuinfo(
    id int,
    stuName varchar(20),
    gender char(1),
    seat int,
    age int,
    majorId int,
    constraint pk primary key(id),                      #The constraint name is optional, and the primary key does not take effect, but no error is reported.
    constraint uq unique(seat),                         #Unique constraint
    constraint ck check(gender='male' or gender='female'),    #Check the constraint. MySql does not support this constraint. No error is reported but it does not take effect
    constraint fk_stuinfo_major foreign key(majorId) references major(id)    #Foreign key constraint
); 

Primary key and unique difference

Constraint name Guarantee uniqueness Allow null How many can there be in a table Allow combination
Primary key × There can be no more than one √ (not recommended)
only There can be more than one √ (not recommended)

Foreign key:

  • Requires foreign key relationships to be set in the slave table
  • 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, and the name is not required.
  • The associated column of the main table must have a Key (usually a primary Key or unique, and a foreign Key is OK but meaningless)
  • 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 constraints when modifying tables

Add a non empty constraint

alter table Table name modify column Column name column type not null;

Add default constraint

alter table Table name modify column Column name column type default Default value;1 

Add primary key

Column level constraint method
alter table Table name modify column Column name column type primary key;
Table level constraint method
alter table Table name add primary key(id);

Add unique

Column level constraint
alter table Table name modify column Column name column type unique;
Table level constraints
alter table Table name add unique(Column name);

Add foreign key

Column level writing is feasible, but it has no effect
Table level constraints
alter table Table name add constraint fk_stuinfo_magor foreign key(magorId) references major(id);

Add common writing (Syntax)

Column level constraint
alter table Table name modify column Column name column type new constraint;
Table level constraints
alter table Table name add constraint Constraint name constraint type(Column name) [References to foreign keys];

Delete constraints when modifying tables

Delete non empty constraints

alter table Table name modify column Column name column type [null];

Delete default constraint

alter table Table name modify column Column name column type;

Delete primary key

alter table Table name modify column id int;
alter table Table name drop primary key;

Delete unique

alter table Table name drop index(Index name) Name when setting unique;

Delete foreign key

alter table Table name drop foreign key(Index name) Name when setting foreign keys;

Identity column

Also known as self growing column, you can insert values manually. The system provides default sequence values. characteristic:

  • The identification column must be matched with a Key (Key refers to primary Key, unique Key, foreign Key...)
  • A table can have at most one identity column
  • The type of identity column can only be numeric
  • The identification column can be displayed through SET auto_increment_increment = 3; Set the step size (global). You can set the starting value by manually inserting the identification column value when inserting a row.

Set identity column when creating table

create table user(
    id int primary key auto_increment,
    name varchar(20)
);

Set identity column when modifying table

alter table Table name modify column id int primary key auto_increment;

Delete identity column when modifying table

alter table Table name modify column id int primary key;

Posted on Fri, 03 Dec 2021 04:53:32 -0500 by pilau