MySQL database Operations (4) Table constraints

MySQL database Operations (4) Table constraints

When you create a table or insert a column, you can constrain the value of the column, and if you insert an entry that does not meet the constraint, an error will be made and the insertion is rejected.

Non-null constraint
' NOT NULL ' field value cannot be null
Cases:

#创建表时添加非空约束CREATE TABLETB1 (IDINT, nameVARCHAR( -)Not   NULL  );INSERT  intoTB1 (ID) VALUE (1); # error, no incoming name# note: Inside MySQL,"'Not equal to null# modify table add non-null constraintALTER TABLETB1 MODIFY IDINT Not   NULL ; #取消非空约束ALTER TABLETB1 MODIFY IDINT;

Unique constraint
' Unique KEY ' ensures that the values in the field are unique
Cases:

#创建表时添加唯一约束CREATE TABLETB2 (IDINT  not NULL  UNIQUE KEY , nameVARCHAR( -) not NULL);INSERT  intoTB2 VALUE (1,'Zhang San');INSERT  intoTB2 VALUE (1,'John Doe'); # Error, ID field value has duplicate # Modify table add unique constraintALTER TABLE' TB2 'ADD  UNIQUE KEY (' name '); #删除唯一约束ALTER TABLETb2 DROP KEY Name: #联合唯一约束ALTER TABLETb2ADDAaINT,AddBbINT;ALTER TABLETb2ADD  UNIQUE KEY (AA,BB);INSERT  intoTB2 VALUE (4,'Canon',1,2);INSERT  intoTB2 VALUE (5,'haha',1,2); # error, (AA,BB) Union field has duplicate # Delete federated unique showCREATE TABLETB2; #查看约束名ALTER TABLETb2 DROP KEY Aa #通过约束名删除约束, the union constraint is named the first column in the Union column by default

PRIMARY KEY constraint
' PRIMARY key ' = = ' not NULL + UNIQUE KEY '
The primary key guarantees the uniqueness of the record, uniquely identifying each piece of data
The primary key is automatically ' not NULL '
Only one primary key can exist per data table
When there is no primary key in a table, the first non-null and unique column that appears is considered to have a primary key.
Cases:

#创建表时添加主键约束CREATE TABLETB3 (IDINT  PRIMARY KEY , nameVARCHAR( -) not NULL); #删除主键约束ALTER TABLETb3DROP  PRIMARY KEY ; #添加主键约束ALTER TABLETb3ADD  PRIMARY KEY (ID); #联合主键CREATE TABLETB4 (id_aINT, id_bINT, contentVARCHAR( -), PRIMARY KEY (id_a,id_b)); #添加联合主键ALTER TABLETb4ADD  PRIMARY KEY (id_a,id_b);

Self-growth
' Auto_increment ' is automatically numbered, usually combined with the primary key. There is only one self-increment in a table.
By default, the starting value is 1, and each increment is 1.
When inserting a record, if a value is explicitly specified for the ' auto_increment ' data column, there are two cases.
Case one, if the inserted value repeats with an existing number, an error message occurs because the value of the Auto_increment data column must be unique;
In case two, if the inserted value is greater than the numbered value, it is inserted into the data column and the next number is incremented from the new value. In other words, you can skip some numbers. If the maximum value of the self-increment sequence is deleted, the value is reused when a new record is inserted. (Can be adjusted large, can not be reduced)
Cases:

#创建表时添加自增长 CREATE TABLE INT PRIMARY KEY auto_increment VARCHAR ()) auto_increment =  - ; # if not written, default starting from 1 # Delete auto Grow ALTER TABLE INT ; #修改表添加自动增长 ALTER TABLE INT auto_increment;

Default Constraints
The ' default ' initial value setting, when inserting a record, automatically assigns a default value if no value is explicitly assigned to the field.
Cases:

#创建表时添加默认约束CREATE TABLETb6 (IDINT PRIMARY KEYAuto_increment,nameVARCHAR( -) not NULL, ageINT  not NULL  DEFAULT  -); #删除默认约束ALTER TABLETb6 MODIFY AgeINT  not NULL;ALTER TABLETb6ALTERAge DROP DEFAULT ; #添加默认约束ALTER TABLETb6 MODIFY AgeINT  DEFAULT  -;ALTER TABLETb6ALTERAge SET DEFAULT   +;

FOREIGN KEY constraints
FOREIGN KEY constraint ' FOREIGN key ', maintaining data consistency, integrity implementation one-to-one or one-to-many relationships.
Requirements for FOREIGN KEY constraints:
The storage engine for the data table can only be InnoDB
The foreign key column and the reference column data type are consistent
The foreign key must be associated to the top of the key, and the general context is the primary key of the other table.
Cases:

#创建表时添加外键约束CREATE TABLE' A ' (a_idINT PRIMARY KEY, A_nameVARCHAR( -) not NULL);CREATE TABLE' B ' (b_idINT PRIMARY KEY, B_nameVARCHAR( -) not NULL, fy_idINT  not NULL,CONSTRAINTab_id FOREIGN KEY (fy_id)REFERENCES' A ' (a_id)); #AB_id是外键的别名, if there is no alias, you cannot delete the foreign key, FY_ID is the column to be associated in this table, ' a ' (a_id) is the A_ID column associated to table A, and the fy_id value must appear in the A_ID column in table A to be used. #删除外键ALTER TABLE' B ' DROP FOREIGN KEY ab_id; #增加外键ALTER TABLE' B 'Add  CONSTRAINT ab_id FOREIGN KEY (fy_id)REFERENCES' A ' (a_id);

MySQL database Operations (4) Table constraints