MySQL notes-table-level constraints

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: Fields 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 id primary key

CREATE TABLE TB_EMP1 (ID int primary key,name varchar), deptid int,salary float); Create an employee table TB_EMP1, combining the primary key with the ID and name

CREATE TABLE Tb_emp3 (ID int,name varchar), DeptID int,salary float,primary key (Id,name));

2. automatic growth ----auto_incerment

Only used for primary key, is numeric type automatic growth

Example:

CREATE TABLE Tb_emp4 (ID int primary KEY auto_increment,name varchar), DeptID int,

Salary float);

3. non-null constraint----not null

C reate table name (column name type not null)

4. default value constraint----Defaults

C reate table name (column name type not null default value)

CREATE TABLE TB_EMP6 (ID int primary KEY auto_increment,name varchar) NOT NULL,

DeptID int NOT null default 1,salary float NOT null default 5000);

5. foreign key ----FOREIGN key

Foreign keys are primarily used to connect data from two tables

C reate table name (column name Type property,constraint foreign key name foreign key ( column name )

R eferences Another table name (column name));

Note: The two fields that establish a foreign key join are of the same type and attribute!!!

Example: establishment of departmental table tb_dept7, employee table TB_EMP7, two tables of DeptID to establish foreign KEY constraints

CREATE TABLE tb_dept7 (ID int primary key,name varchar (20));

Note: The departmental table must first insert data to create an employee table

CREATE TABLE TB_EMP7 (ID int primary KEY auto_increment,name varchar) NOT NULL,

DeptID int NOT null default 1,salary float NOT null default 5000,constraint fk_emp7_dept7 foreign key (deptid) references T B_DEPT7 (ID));

Delete foreign key: Because you can have more than one foreign key, you have to have a name

To remove the table data that establishes the foreign key connection, first cancel the foreign key connection

ALTER TABLE name drop FOREIGN key foreign key name;

Delete PRIMARY key: ALTER TABLE name drop PRIMARY key;

If the primary key field is self-increment, can not be directly deleted, you must first change the definition of the addition of the added!

MySQL notes-table-level constraints