MySQL data constraint

Non-null constraint

CREATE TABLE USER (    INTnotNULL,--  non-null constraint    VARCHAR(10  ),    VARCHAR(DEFAULT' Shandong province '  --  default value )

Unique constraint

CREATE TABLE USER (    INTunique,--  Unique constraint    VARCHAR() ,    VARCHAR(theDEFAULT' Shandong province '–  Default value )

--PRIMARY KEY constraint--NOTE:--1) Typically, each table will have a primary key field set. Used to mark the uniqueness of each record in a table. ---2) It is recommended that you do not select a field that contains business meaning for the table as the primary key, and it is recommended that you design an ID field for each table that is non-business meaning independently.

CREATE TABLE USER(UIDINT PRIMARY KEY,--PRIMARY KEY constraint non-null + uniqueUnameVARCHAR(Ten), AddressVARCHAR( -)DEFAULT 'Shandong Province' --Default Value)INSERT  into USER(Uid,uname)VALUES('Zhang San');

Self-growth constraints

CREATE TABLE USER(UIDINT PRIMARY KEYAuto_increment,--the self-growth constraint must be a primary keyUnameVARCHAR(Ten), AddressVARCHAR( -)DEFAULT 'Shandong Province' --Default Value)INSERT  into USER(uname)VALUES('Zhang San');

0 Filling

CREATE TABLE USER(UIDINT(5) ZerofillPRIMARY KEYAuto_increment,--self-growth constraint must be primary key, 0 paddingUnameVARCHAR(Ten), AddressVARCHAR( -)DEFAULT 'Shandong Province' --Default Value)INSERT  into USER(uname)VALUES('Zhang San');

--Delete from empty table can not delete constraint--tauncate table clears the constraint

FOREIGN key

CREATE TABLEDept (DeptIDINT PRIMARY KEYauto_increment, DeptnameVARCHAR(Ten))SELECT *  fromDept;SELECT *  fromEMP;INSERT  intoDept (Deptname)VALUES('Software Development Department');INSERT  intoDept (Deptname)VALUES('Software Testing Department');CREATE TABLEEMP (EidINT PRIMARY KEYauto_increment, enameVARCHAR(5), DeptIDINT,    CONSTRAINTEmp_dept_fkFOREIGN KEY(DeptID)REFERENCESDept (deptid))INSERT  intoEMP (Ename,deptid)VALUES('Zhang San',1);

1) The constrained table is called the secondary table, the table that constrains others is called the main table, and the foreign key is set on the secondary table!!!

2) Main Table Reference field universal primary key!

3) Add data: First add the Main table, then add the secondary table

4) Modify the data: Modify the secondary table first, then modify the main table

5) Delete the data: Delete the secondary table before deleting the main table

MySQL data constraint