Article From:https://www.cnblogs.com/zzq123/p/9967738.html

Data constraint

1 What are data constraints?

Constraints on the data of user operation tables

2 Default value

Function: Users use default values when they do not insert values into fields that use default values.

CREATE TABLE user01(

      uid INT,

      uname VARCHAR(10),

      address VARCHAR(20)DEFAULT’Ionia

)

INSERT INTO user01 VALUES (1,’Tyrone,’Bill Givolt’;

INSERT INTO user01(uid,uname) VALUES (2,’Aralia ‘);

INSERT INTO user01 VALUES (3,’Fizz, NULL);

SELECT * FROM user01;

 

 

 

3 Non empty constraint

Function: Restrict fields must be assigned

Be careful:

      1)Non-null characters must be assigned

      2)Null cannot be assigned to non-null characters

CREATE TABLE user01(

      uid INT NOT NULL,

      uname VARCHAR(10),

      address VARCHAR(20)DEFAULT’Ionia

)

INSERT INTO user01 VALUES(1,’Carter’,’Shadow Island’;

You can insert the Carter entry, but when you execute the following

INSERT INTO user01(uname) VALUES(‘Trundle);

 

4 Only

Function: The value of a field cannot be duplicated

Be careful:

      1)Unique fields can be inserted into null

      2)Unique fields can be inserted into multiple nulls

CREATE TABLE user01(

      uid INT NOT NULL,

      uname VARCHAR(10) UNIQUE,

      address VARCHAR(20)DEFAULT’Ionia

)

INSERT INTO user01(uid,uname) VALUES (1,’Anne ‘);

When you re-enter the above sentence

 

 

5 Primary key

Function: Nonempty + Unique

Note:

      1)Under normal circumstances,Each table has a primary key field.Used to mark the uniqueness of each record in a table.

      2)It is recommended not to select the fields with business meaning as the primary key, but to design an ID field with non-business meaning independently for each table.

CREATE TABLE emp(

      eid INT PRIMARY KEY AUTO_INCREMENT,

      ename VARCHAR(10),

      deptid INT

)

INSERT INTO emp(ename,deptid) VALUES(‘Zhang three ‘, 1);

INSERT INTO emp(ename,deptid) VALUES(‘Li four ‘, 2);

SELECT * FROM emp;

 

 

Among them, PRIMARY KEY is the main key AUTO_INCREMENT, which is self-increasing.

Self-growing fields can be incremented automatically without assignment

DELETE FROM student; — Can not affect self-growth constraints

TRUNCATE TABLE student; — Can affect self-growth constraints

6 foreign key

Role: Constraining data for two tables

          

Two tables appear:

      Solve the problem of high data redundancy: create a table independently

      For example: Employees’and Departments’ Tables

      Problem: When inserting employee table data, the Department ID field of employee table can be easily inserted!!!!!     

 

      Using foreign key constraints: Constraints to insert Department ID field values into the employee table

 

      Solution: Add a foreign key constraint to the Department ID field of the employee table

CREATE TABLE dept(    Build main table first

      id INT PRIMARY KEY AUTO_INCREMENT,

      dname VARCHAR(15)

)

CREATE TABLE emp(     Post built schedule

      eid INT PRIMARY KEY AUTO_INCREMENT,

      ename VARCHAR(10),

      deptid INT,

      CONSTRAINT emp_dept_fk FOREIGN KEY(deptid)REFERENCES dept(id)

)

 

INSERT INTO dept(dname) VALUES (‘Test department ‘);

INSERT INTO dept(dname) VALUES (‘Marketing department).

INSERT INTO dept(dname) VALUES (‘Sales Department)

SELECT * FROM dept;

INSERT INTO emp(ename,deptid) VALUES(‘Pig eight quit ‘, 3);

INSERT INTO emp(ename,deptid) VALUES(‘Sun Wukong ‘, 2);

INSERT INTO emp(ename,deptid) VALUES(‘White bone essence, 1);

SELECT * FROM emp;

Be careful:

                 1)Restricted tables are called sub-tables, others’tables are called master tables, and foreign keys are set on sub-tables!!!

                 2)The reference field of the main table is the main key.

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

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

                     5)Delete data:First delete the secondary table, then delete the main table

 

 

 

When inserting values that are not available in the main table

INSERT INTO emp(ename,deptid) VALUES(‘Tang San Zang, 4);

 

 

l  The Difference between Two Kinds of Full Table Deletion

First, we create a new table test to insert data in which the primary key ID grows by itself.

CREATE TABLE test(

      id INT PRIMARY KEY AUTO_INCREMENT,

      tname VARCHAR(10)

)

INSERT INTO test(tname) VALUES(‘Xiaohong ‘;

INSERT INTO test(tname) VALUES(‘Xiaolan ‘;

INSERT INTO test(tname) VALUES(‘Small green);

 

 

First, delete from delete

DELETE FROM test;

After deletion, add: INSERT INTO test (tname) VALUES (‘Xiaohong’);

INSERT INTO test(tname) VALUES(‘Xiaolan ‘;

INSERT INTO test(tname) VALUES(‘Small green);

At this time:

 

The truncate table runs as follows

 

Link of this Article: Data constraint

Leave a Reply

Your email address will not be published. Required fields are marked *