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

What is restraint for what?

Constraints are actually the constraints of the data in the table.

Function: the purpose of adding constraints to the table at design time is to ensure that the records in the table are complete and valid

For example, some fields are unique, and some fields are constrained to foreign keys.

Type of constraint:

Non-null constraint (not null)

Uniqueness constraint (unique)

Primary key constraint (primary key) competition

Foreign key constraint (foreign key) FK

Check constraints (currently not supported by MySQL, supported by Oracle)

A, non-empty constraint

Fields constrained with not null cannot be null values and must be given specific data

Create a table and add a non-empty constraint to the field (create a user table, the user name cannot be empty)

Create table blog (

Id int (12) not null

)

When insert inserts data, if inserting id directly reports an error for null.

B, uniqueness constraint

Fields constrained by unique, unique and non-repeatable, but can be null

Create a table to ensure that mailbox addresses are unique (column-level constraints)

Create table blog (

Id int (12) not null UNIQUE

)

Table level constraint

Create table blog (

Id int (12) not null

UNIQUE (id)

)

If you insert the same id, you will get an error.

Use table-level constraints to jointly constrain multiple fields

A joint constraint that indicates that two or more fields are equal to another record at the same time, an error is reported

Create table blog (

Id int (12) not null

Name varchar (20) not null

Unique (id,name)

)

If it is the same as the federated field, an error is reported

A table-level constraint can give a name to the constraint (it is convenient to delete the constraint later with this name)

Create table blog (

Id int (12) not null

Name varchar (20) not null

CONSTRAINT t_id_name unique (id,name)

)

Constraint is a constraint keyword, named by t_user_email_unique himself.

C, primary key constraint (primary key) competition

There must be a primary key when designing a table

Primary key constraint

Primary key field

Primary key value

When a primary key constraint is added to a field in the table, the field is the primary key field, and every data that appears in the primary key field is called the primary key value

Primary key constraint is different from "not null unique": after adding a primary key constraint to a field, the field cannot be repeated or empty, and the effect is the same as the "not null unique" constraint, but different in nature.

In addition to "not null unique", the primary key constraint also adds "index-index" by default.

A table should have a primary key field. If not, the table is invalid.

Primary key value: the unique identification of the current row data and the identity of the current row data

Even if the related data of the two rows of records in the table are the same, they are considered to be two different rows of records because the primary key values are different.

Classified by the number of fields for primary key constraints: whether it is a single primary key or a compound primary key, there can be only one primary key constraint in a table (there can be only one constraint, but it can affect several fields).

Single primary key: add a primary key constraint to a field

Compound primary key: add a primary key constraint to multiple fields (can only be defined at the table level)

1) single primary key (column-level definition)

Mysql > create table t_user (

-> id int (10) primary key

> name varchar (30)

->)

Query OK, 0 rows affected (0.07 sec)

2) single primary key (table-level definition)

Mysql > create table t_user (

-> id int (10)

-> name varchar (30) not null

-> constraint t_user_id_pk primary key (id)

->)

Query OK, 0 rows affected (0.01 sec)

3) compound primary key (table-level definition)

Mysql > create table t_user (

-> id int (10)

-> name varchar (30) not null

-> email varchar (128) unique

-> primary key (id,name)

->)

Query OK, 0 rows affected (0.05 sec)

A self-increasing number is provided in the MySQL database, which is specially used to automatically generate primary key values, which are automatically generated without user maintenance, starting with 1 and incrementing by 1 (auto_increment)

Mysql > create table t_user (

-> id int (10) primary key auto_increment

-> name varchar (30) not null

->)

Query OK, 0 rows affected (0.03 sec)

Insert two rows of records, and the id primary key value will automatically increase.

Mysql > insert into t_user (name) values ('jay')

Query OK, 1 row affected (0.04 sec)

Mysql > insert into t_user (name) values ('man')

Query OK, 1 row affected (0.00 sec)

Mysql > select * from t_user

+-+ +

| | id | name |

+-+ +

| | 1 | jay |

| | 2 | man |

+-+ +

2 rows in set (0.00 sec)

D, foreign key constraint (foreign key) FK

Can only be a table-level definition

Foreign key (classno) references t_class (cno)

Foreign key constraints are mainly used to maintain data consistency between two tables.

Foreign key constraint

Foreign key field

Foreign key value

Relationship among foreign key constraints, foreign key fields, and foreign key values: after a foreign key constraint is added to a field, the field is called a foreign key field, and each data in the foreign key field is a foreign key value

Classify by the number of fields constrained by foreign keys: a, single foreign keys: add foreign key constraints to a field

B, compound foreign key: add a foreign key constraint to multiple fields

A table can have multiple foreign key fields (unlike primary keys)

Chestnut: storing students' class letters.

Mysql > drop table if exists t_student

Mysql > drop table if exists t_class

Mysql > create table t_class (

-> cno int (10) primary key

-> cname varchar (128) not null unique

->)

Mysql > create table t_student (

-> sno int (10) primary key auto_increment

-> sname varchar (30) not null

-> classno int (3)

-> foreign key (classno) references t_class (cno)

->)

Mysql > insert into t_class (cno,cname) values (100mt.)

Mysql > insert into t_class (cno,cname) values (200pm)

Mysql > insert into t_student (sname,classno) values ('jack',100)

Mysql > insert into t_student (sname,classno) values ('lucy',100)

Mysql > insert into t_student (sname,classno) values ('king',200)

Class table t_class:

Mysql > select from t_class

+-+ +

| | cno | cname |

+-+ +

| | 100 | aaaaaaxxxxxx |

| | 200 | oooooopppppp |

+-+ +

Student form t_student:

Mysql > select from t_student

+-+

| | sno | sname | classno | |

+-+

| | 1 | jack | 100 | |

| | 2 | lucy | 100 | |

| | 3 | king | 200 | |

+-+

Find out the class name of each student in the above table:

Mysql > select s. Join t_class c. From t_student s join t_class c on s.classno=c.cno

+-+

| | sno | sname | classno | cno | cname | |

+-+

| | 1 | jack | 100 | 100 | aaaaaaxxxxxx |

| | 2 | lucy | 100 | 100 | aaaaaaxxxxxx |

| | 3 | king | 200 | 200 | oooooopppppp |

+-+

This is the one-to-many relationship model of data entities: foreign keys are added to the side of the many to constrain.

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.