Mysql---integrity constraints

Integrity constraints---to ensure data integrity and consistency

KEY Point:

• Not NULL vs. default
• Unique
• Primary key
• Auto_increment
• FOREIGN key

A NOT NULL with default

1. Is it possible to empty

NULL NULL

Not NULL is not nullable

2. Default value, default value is null when not set, default value is set default when setting default value

A. The default value can be null:

B. Set not NULL, cannot be empty when inserting a value

C. After setting the ID field with a default value, you can insert null if the ID field is NULL or NOT NULL, insert null defaults to fill default value specified by default

Two unique

1. Single-row unique

2. Joint unique

3. Combine unique

Three primary key

1. Primary key equals NOT NULL the value of the +unique field is unique and not empty

Mysql>CREATE TABLE T10 (Idintprimary KEY,NameChar( -)    - ); Query OK,0Rows Affected (0.04sec) MySQL>INSERT INTO T10 values(1,"Xiaoma"),    (2,"Alex"),    (3,"Wusir"); Query OK,3Rows Affected (0.01sec) Records:3Duplicates:0Warnings:0MySQL>Select* fromT10;+----+--------+| ID | Name |+----+--------+|1| Xiaoma | |2| Alex | |3| Wusir |+----+--------+3Rowsinch Set(0.00sec) MySQL>INSERT INTO T10 values(1,"Tailiang"); ERROR1062(23000): Duplicate Entry'1'  forKey'PRIMARY'MySQL>desc T10;+-------+----------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+----------+------+-----+---------+-------+| ID |int( One)  | NO | PRI |       NULL | || name |Char( -) |     YES | |       NULL | |+-------+----------+------+-----+---------+-------+2Rowsinch Set(0.00Sec

View Code

2. A table can have a single-row master key, or you can have multiple columns of the master key

mysql> CREATE TABLE T12 (    char()    ,int,    - > primary KEY (Ip,port)    ,0 rows affected (0.05 sec)

View Code

3. The storage Engine is (INNODB) by default: For the InnoDB storage engine, a table must have a primary key.

Four auto_increment constrained fields are autogrow, constrained fields must be constrained by key at the same time

1. Do not specify ID, then autogrow

Mysql>CREATE TABLE T14- (    Idintprimary Key auto_increment,Name varchar ( the) notNULL); Query OK,0Rows Affected (0.05sec) MySQL> INSERT into t14 values ("Taibai"),("too Dark"); ERROR1136(21s01): Column Count doesn't match value count at row 1mysql> INSERT into T14 (name) VALUES ("Taibai"),("too Dark"); Query OK,2Rows Affected (0.00sec) Records:2Duplicates:0Warnings:0MySQL>Select* fromt14;+----+--------+| ID | Name |+----+--------+|1| Taibai | |2| Too black |+----+--------+2Rowsinch Set(0.00Sec

View Code

2. You can also specify an ID

3. For the self-increment field, after deleting with delete and inserting the value, the field continues to grow by the position before deletion

4. Note:

A. Step auto_increment_increment, default = 1

B. Offset from start Auto_increment_offset, default is 1

The default step and start offset can be changed by setting the above two values

Mysql>Use db1database changed# to create a table T16mysql>CREATE TABLE T16 (Idintprimary Key auto_increment,Name varchar (Ten) notNULL- ); Query OK,0Rows Affected (0.04sec) #插入数据mysql>INSERT INTO t16 values(1,"Alex"),    (2,"Wusir"),    (3,"Egon"),    (4,"Xiaomage"); Query OK,4Rows Affected (0.01sec) Records:4Duplicates:0Warnings:0#显示t16详细数据mysql>Select* fromt16;+----+----------+| ID | Name |+----+----------+|1| Alex | |2| Wusir | |3| Egon | |4| Xiaomage |+----+----------+4Rowsinch Set(0.00sec) #删除 ID=2 of fields record MySQL> Delete fromT16whereId=2; Query OK,1Row affected (0.00sec) MySQL>Select* fromt16;+----+----------+| ID | Name |+----+----------+|1| Alex | |3| Egon | |4| Xiaomage |+----+----------+3Rowsinch Set(0.00sec) #删除 ID=4 of fields record MySQL> Delete fromT16whereId=4; Query OK,1Row affected (0.01sec) MySQL>Select* fromt16;+----+------+| ID | Name |+----+------+|1| Alex | |3| Egon |+----+------+2Rowsinch Set(0.00sec) #插入新的纪录mysql>INSERT INTO t16 values("haha"); ERROR1136(21s01): Column Count doesn't match value count at row 1Mysql>INSERT into T16 (name) values("haha"); Query OK,1Row affected (0.01sec) MySQL>Select* fromt16;+----+------+| ID | Name |+----+------+|1| Alex | |3| Egon | |5| haha |#新插入的字段依然按照之前顺序排列+----+------+3Rowsinch Set(0.00sec) #查看可用的 start auto_inc the word MySQL> Show variables like'auto_inc%';+--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment |1|#步长默认为1| Auto_increment_offset |1|#偏移量默认为1+--------------------------+-------+2Rowsinch Set,1Warning (0.02sec) MySQL>SetSession auto_increment_increment=5; #设置步长为5Query OK,0Rows Affected (0.01sec) MySQL>SetSession auto_increment_offset=2; #设置偏移量为2Query OK,0Rows Affected (0.00sec) #确认设置成功mysql> Show variables like'auto_inc%';+--------------------------+-------+| variable_name | Value |+--------------------------+-------+| auto_increment_increment |5|| Auto_increment_offset |2|+--------------------------+-------+2Rowsinch Set,1Warning (0.00sec) #重新插入新纪录mysql>INSERT into T16 (name) values(" filling and filling irrigation"); Query OK,1Row affected (0.00sec) #查看新纪录idmysql>Select* fromt16;+----+--------------+| ID | Name |+----+--------------+|1| Alex | |3| Egon | |5| haha | |7| Irrigation and Irrigation |+----+--------------+4Rowsinch Set(0.00sec) MySQL>INSERT into T16 (name) values("Rich"); Query OK,1Row affected (0.01sec) MySQL>Select* fromt16;+----+--------------+| ID | Name |+----+--------------+|1| Alex | |3| Egon | |5| haha | |7| Filling and filling irrigation | | A| Rich |+----+--------------+5Rowsinch Set(0.00Sec

View Code

Five foreign key

The difference between the six delete and the truncate

Mysql---integrity constraints