Shulou(Shulou.com)06/01 Report--

Constraints are also called integrity constraints (integrity constraint)

What is integrity?

Integrity means that the data stored in the database is meaningful and correct.

What is restraint?

In order to ensure the correctness and compatibility of the data, some constraints or rules are proposed for the relational model.

Note: constraints generally act on fields

What are the constraints?

Non-empty, unique, default, primary key, foreign key, self-increment

Syntax:

Field name field type [not null | unique | default default value | auto_increment]

1. Default value

Mysql > create table T6 (name varchar (10), sex char (10) default 'male')

Mysql > insert into T6 values ()

Mysql > select * from T6

+-+ +

| | name | sex |

+-+ +

| | NULL | male |

+-+ +

1 row in set (0.00 sec)

Default value: when the user inserts data into the table and specifies the value of the field, the value is inserted; otherwise, the default value is inserted.

There are two ways to modify the default value of a field in an existing table

Alter table table name modify field name field type default default

Alter table table name alter field name set default default

Mysql > alter table T6 alter name set default 'tom'

Mysql > insert into T6 values ()

Mysql > select * from T6

+-+ +

| | name | sex |

+-+ +

| | NULL | male |

| | tom | male |

+-+ +

2 rows in set (0.00 sec)

2. Non-empty not null

Mysql > select * from T6 where name is null; / / query rows whose name field is null

Mysql > select * from T6 where name is not null; / / query rows whose name field is not null

Mysql > create table T7 (id int not null,name char (10))

Mysql > insert into T7 values (); / / converts id fields that are not allowed to be empty to 0

Mysql > select * from T7

+-+ +

| | id | name |

+-+ +

| | 0 | NULL |

| | 0 | NULL |

+-+ +

2 rows in set (0.00 sec)

Mysql > alter table T7 modify name char (10) not null

Mysql > select * from t7; / / the field type is string, and non-null constraints will convert null values to empty strings.

+-+ +

| | id | name |

+-+ +

| | 0 |

| | 0 |

+-+ +

2 rows in set (0.00 sec)

3. Unique unique

Mysql > create table T8 (id int unique,name char (10))

Mysql > insert into t8 values (); / / Note: the uniqueness constraint is not valid for null values

Mysql > insert into T8 values ()

Mysql > select * from T8

+-+ +

| | id | name |

+-+ +

| | NULL | NULL |

| | NULL | NULL |

+-+ +

2 rows in set (0.00 sec)

Mysql > insert into T8 values (1)

Mysql > insert into T8 values (1MIT Mary')

ERROR 1062 (23000): Duplicate entry'1' for key 'id'

Mysql > alter table T8 modify name char (10) unique

Mysql > insert into T8 values (2MIT Mary')

Mysql > insert into T8 values

ERROR 1062 (23000): Duplicate entry 'tom' for key' name'

4. Self-increasing auto_increment

Request:

1) this field must be numeric

2) there should be a unique index or primary key on the field

Mysql > create table T9 (id int primary key auto_increment)

Mysql > desc T9

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | NULL | auto_increment |

+-+ +

1 row in set (0.00 sec)

Mysql > insert into T9 values (); / / insert 1

Mysql > insert into T9 values (3); / / insert 3

The following points are explained:

1) when a break occurs in the self-increasing field, the value will continue to increase from the maximum value.

2) when delete deletes the maximum value, the next value continues to increase from the maximum value before deletion

3) when the truncate table, the value is recalculated from 1

5. Primary key primary key

The primary key is a special field in the table that uniquely identifies each record in the table.

A table can have at most one primary key.

The purpose of the primary key: quickly locate data

The condition that the primary key needs to meet: non-empty and unique

Primary key = = not null + unique

1) use a single field as the primary key

A. Specify the primary key constraint directly after the field (column-level constraint, default is NULL)

Mysql > create table T10 (id int primary key,age int,name char (10))

Mysql > desc T10

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | NULL |

| | age | int (11) | YES | | NULL |

| | name | char (10) | YES | | NULL |

+-+ +

Mysql > insert into T10 (id) values (1)

Mysql > insert into T10 (id) values (1)

ERROR 1062 (23000): Duplicate entry'1' for key 'PRIMARY'

Mysql > insert into T10 values ()

Mysql > insert into T10 values ()

ERROR 1062 (23000): Duplicate entry'0' for key 'PRIMARY'

B. specify the primary key after all the fields of the entire table have been defined (table-level constraint, default is 0)

Mysql > create table T11 (id int,name char (5), primary key (id))

Mysql > desc T11

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | 0 | |

| | name | char (5) | YES | | NULL |

+-+ +

2 rows in set (0.00 sec)

Mysql > insert into T11 values ()

Mysql > insert into T11 values ()

ERROR 1062 (23000): Duplicate entry'0' for key 'PRIMARY'

Mysql > insert into T11 values (1)

Mysql > select * from T11

+-+ +

| | id | name |

+-+ +

| | 0 | NULL |

| | 1 | hi |

+-+ +

2 rows in set (0.00 sec)

2) multiple fields are combined as primary keys

Mysql > desc mysql.user\ G / / user and host fields are combined as primary keys

* * 1. Row *

Field: Host

Type: char (60)

Null: NO

Key: PRI

Default:

Extra:

* 2. Row * *

Field: User

Type: char (16)

Null: NO

Key: PRI

Default:

Extra:

* 3. Row * *

Field: Password

Type: char (41)

Null: NO

Key:

Default:

Extra:

Note: a federated primary key can be defined only after all fields have been defined.

Mysql > create table T12 (id int,name char (2), age int,primary key (id,name))

Mysql > desc T12

+-+ +

| | Field | Type | Null | Key | Default | Extra | |

+-+ +

| | id | int (11) | NO | PRI | 0 | |

| | name | char (2) | NO | PRI | |

| | age | int (11) | YES | | NULL |

+-+ +

Mysql > insert into T12 (id) values (1)

Mysql > select * from T12

+-- +

| | id | name | age | |

+-- +

| | 0 | | NULL |

| | 1 | | NULL |

+-- +

2 rows in set (0.00 sec)

Mysql > insert into T12 (name) values ('a')

Mysql > select * from T12

+-- +

| | id | name | age | |

+-- +

| | 0 | | NULL |

| | 0 | a | NULL |

| | 1 | | NULL |

+-- +

3 rows in set (0.00 sec)

6. Foreign key foreign key

Foreign key: the data of one table depends on the data of the primary key column of another table. If there is no value in the primary key column, it cannot appear in the foreign key field.

Primary and foreign keys are like adhesives that connect multiple tables.

Conditions for creating foreign keys:

1) the storage engine is innodb

2) the data types of the associated fields should be consistent.

3) it is best to build an index on the foreign key column (the purpose is to reduce the scanning range, it is OK not to create it, but it only affects performance)

Example:

Dept: Department tabl

Emp: employee tabl

Mysql > create table dept (dno int,dname char (10), primary key (dno))

Mysql > create table emp (eno int,e_dno int,ename char (15), index (e_dno), foreign key (e_dno) references dept (dno))

Insert data into the parent table

Mysql > insert into dept values (1), (2)), (3))

Insert data into a child table

Mysql > select * from dept

+-+ +

| | dno | dname |

+-+ +

| | 1 | sa |

| | 2 | dba |

| | 3 | manager |

+-+ +

3 rows in set (0.00 sec)

Mysql > insert into emp values.

Mysql > select * from emp

+-+

| | eno | e_dno | ename | |

+-+

| | 100 | 3 | Tom |

+-+

1 row in set (0.00 sec)

Mysql > insert into emp values (101memorials 4 minutes Mary'); / / counterexample: insert a department number that does not exist in the parent table

ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`up1`.`emp`, CONSTRAINT `emp_ibfk_ 1` FOREIGN KEY (`e_ dno`) REFERENCES `dept` (`dno`))

Mysql > delete from dept where dno=2

Mysql > delete from dept where dno=3

ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`up1`.`emp`, CONSTRAINT `emp_ibfk_ 1` FOREIGN KEY (`e_ dno`) REFERENCES `dept` (`dno`))

Summary:

1) the data in the child table depends on the parent table, and no values in the parent table can be inserted into the child table.

2) the records dependent on the quilt table in the parent table cannot be deleted.

The method to delete dependent rows in the parent table:

1) Delete foreign key constraint

2) specify options for cascading operations

On delete cascade: cascading deletion

On update cascade: cascading updates

Mysql > drop table emp

Mysql > create table emp (eno int,e_dno int,ename char (15), index (e_dno), foreign key (e_dno) references dept (dno) on delete cascade on update cascade); / / complete foreign key creation

Mysql > insert into emp values, (101meme 3recorder Mary'), (103meme 1Jing Jake')

Mysql > select * from dept

+-+ +

| | dno | dname |

+-+ +

| | 1 | sa |

| | 3 | manager |

+-+ +

2 rows in set (0.00 sec)

Mysql > select * from emp

+-+

| | eno | e_dno | ename | |

+-+

| | 100 | 1 | Tom |

| | 101 | 3 | Mary |

| | 103 | 1 | Jack |

+-+

3 rows in set (0.00 sec)

Mysql > delete from dept where dno=1

Query OK, 1 row affected (0.02 sec)

Mysql > select * from emp

+-+

| | eno | e_dno | ename | |

+-+

| | 101 | 3 | Mary |

+-+

1 row in set (0.00 sec)

Mysql > update dept set dno=100 where dno=3

Query OK, 1 row affected (0.07 sec)

Rows matched: 1 Changed: 1 Warnings: 0

Mysql > select * from emp

+-+

| | eno | e_dno | ename | |

+-+

| | 101 | 100 | Mary |

+-+

1 row in set (0.00 sec)

With cascading delete and cascade modification options, when the data in the parent table is deleted or updated, the relevant data in the child table will change accordingly.

Delete foreign key

Alter table table name the name of the drop foreign key foreign key

Mysql > show create table emp\ G / / the red font is the name of the foreign key

* * 1. Row *

Table: emp

Create Table: CREATE TABLE `emp` (

`eno` int (11) DEFAULT NULL

`e_ dno` int (11) DEFAULT NULL

`ename` char (15) DEFAULT NULL

KEY `e_ dno` (`e_ dno`)

CONSTRAINT `emp_ibfk_ 1` FOREIGN KEY (`e_ dno`) REFERENCES `dept` (`dno`) ON DELETE CASCADE ON UPDATE CASCADE

) ENGINE=InnoDB DEFAULT CHARSET=utf8

Mysql > alter table emp drop foreign key emp_ibfk_1

Mysql > show create table emp\ G

* * 1. Row *

Table: emp

Create Table: CREATE TABLE `emp` (

`eno` int (11) DEFAULT NULL

`e_ dno` int (11) DEFAULT NULL

`ename` char (15) DEFAULT NULL

KEY `e_ dno` (`e_ dno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8

1 row in set (0.00 sec)

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.