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

This article mainly introduces the explanation of mysql level constraints, which involves things, learned from the theoretical knowledge, there are many books, literature for your reference, from the perspective of practical significance, accumulated years of practical experience can be shared with you.

Table-level constraints

1. Primary key constraint-primary key

Primary key: a combination of one or more columns in a table that requires the data of that column to be unique

Single field primary key: field name data type property primary key

Multi-field primary key: primary key (field 1, field 2)

The value of the primary key column cannot be empty!

Example: create an employee table tb_emp1 with id as the primary key

Create table tb_emp1 (id int primary key,name varchar (25), deptid int,salary float); create an employee table tb_emp1 with id and name as the combined primary keys

Create table tb_emp3 (id int,name varchar (25), deptid int,salary float,primary key (id,name))

two。 Automatic growth-auto_incerment

It only acts on the primary key, which is a numerical automatic growth.

Example:

Create table tb_emp4 (id int primary key auto_increment,name varchar (25), deptid int

Salary float)

3. Non-empty constraint-not null

Create table name (column name type not null)

4. Default value constraint-default

Create table name (column name type not null default numeric value)

Create table tb_emp6 (id int primary key auto_increment,name varchar (25) not null

Deptid int not null default 1 minute float not null default 5000)

5. Foreign key-foreign key

Foreign keys are mainly used to join the data of two tables.

Create table name (column name type attribute, constraint foreign key name foreign key (column name)

References another table name (column name)

Note: the types and properties of the two fields that establish a foreign key connection should be the same!

Example: establish department table tb_dept7 and employee table tb_emp7, and set up foreign key constraints on the deptid of the two tables

Create table tb_dept7 (id int primary key,name varchar (20))

Note: the department table must insert the data before the employee table can be established.

Create table tb_emp7 (id int primary key auto_increment,name varchar (25) not null

Deptid int not null default 1J float not null default 5000 J constraint fk_emp7_dept7 foreign key (deptid) references tb_dept7 (id)

Delete foreign keys: because you can have multiple foreign keys, you should have a name

When you want to delete table data that establishes a foreign key connection, you must first remove the foreign key connection

Alter table table name drop foreign key foreign key name

Delete primary key: alter table table name drop primary key

If the primary key field is self-increasing, it can not be deleted directly, you should first change the definition to delete the self-increment!

Read the above introduction mysql table level constraint explanation, hope to bring some help to everyone in the practical application. Due to the limited space in this article, there will inevitably be deficiencies and areas that need to be supplemented. You can continue to pay attention to the industry information section and will update your industry news and knowledge regularly. If you need more professional answers, you can contact us on the official website for 24-hour pre-sales and after-sales to help you answer questions at any time.

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.