Database strengthening chapter (1)

Data Constraints 2.1 What data constraints

Constrain the data on the User action table

2.2 Default value

function: The default value is used when the user does not insert a value into a field that uses the default value .

Attention:

1) It is possibleto insert NULL for the default value field .

2) The default value field can be inserted non- null

--1.1 default value CREATE TABLE Student ( ID INT, NAME VARCHAR (20), Address VARCHAR default ' guangzhou tianhe '-- defaults ) DROP TABLE student; --MySQL automatically assigns a default value to the field when no value is inserted INSERT into student (Id,name) VALUES (1, ' Zhang San '); -- Note: The default value of the field is allowed to be null INSERT into student (id,name,address) VALUE (2, ' John Doe ', NULL); INSERT into student (id,name,address) VALUE (3, ' Harry ', ' guangzhou panyu ');

2.3 non-empty

function: The limit field must be assigned a value

Attention:

1) non-null characters must be assigned

2) non-null characters cannot be assigned null

--1.2 non-empty -- requirements: gender field must have a value (not null) CREATE TABLE Student ( ID INT, NAME VARCHAR (20), Gender VARCHAR (2) NOT null-- non-null ) -- non-empty fields must be assigned values INSERT into student (Id,name) VALUES (1, ' John Doe '); -- non-null characters cannot insert null INSERT into student (Id,name,gender) VALUES (1, ' John Doe ', NULL);

2.4 Unique

function: The value of a field cannot be duplicated

Attention:

1) Unique field can be inserted null

2) Unique field can insert multiple null

--1.3 unique CREATE TABLE Student ( ID INT Unique,-- unique NAME VARCHAR (20) ) INSERT into student (Id,name) VALUES (1, ' ZS '); INSERT into student (Id,name) VALUES (1, ' Lisi '); --ERROR 1062 (23000): Duplicate entry ' 1 ' for key ' ID ' INSERT into student (Id,name) VALUES (2, ' Lisi ');

2.5 primary key

function: non-null + unique

Attention:

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 a non-business-meaning ID field independently for each table .

--1.4 PRIMARY KEY (non-null + unique) DROP TABLE student; CREATE TABLE Student ( ID INT PRIMARY Key,-- primary key NAME VARCHAR (20) ) INSERT into student (Id,name) VALUES (1, ' Zhang San '); INSERT into student (Id,name) VALUES (2, ' Zhang San '); --INSERT into student (Id,name) VALUES (1, ' John Doe ');-- violation of the unique constraint: Duplicate entry ' 1 ' for key ' PRI MARY ' --INSERT into student (name) value (' John Doe ');-- violation of non-null constraint: ERROR 1048 (23000): Column ' id ' can not is null

2.6 Self-growth

function: Auto Increment

--1.5 self-growth CREATE TABLE Student ( ID INT (4) Zerofill PRIMARY KEY auto_increment,-- self-growing, starting from 0 zerofill 0 padding NAME VARCHAR (20) ) -- self-growth fields can be unassigned, automatically incremented INSERT into student (NAME) VALUES (' Zhang San '); INSERT into student (NAME) VALUES (' John Doe '); INSERT into student (NAME) VALUES (' Harry '); SELECT * from student; -- cannot affect self-growth constraints DELETE from student; -- can affect self-growth constraints TRUNCATE TABLE student;

2.7 foreign key

Role: Constrain data for both tables

There are two types of tables:

addressing high data redundancy issues: separate out a sheet

For example:  Employee Table and departmental tables

The problem arises: when inserting employee table data, the Department of the employee table The ID field can be inserted arbitrarily!!!!!

using FOREIGN KEY constraints: constrain the department that inserts the employee table ID field Value

Workaround: in the Department of the employee table ID Field add a foreign key constraint

-- Department table (main table) CREATE TABLE Dept ( ID INT PRIMARY KEY, Deptname VARCHAR (20) ) -- Modify the Employee table (sub-table / from table) CREATE TABLE Employee ( ID INT PRIMARY KEY, EmpName VARCHAR (20), DeptID INT,-- Change Department name to department ID -- declaring a foreign key constraint CONSTRAINT emlyee_dept_fk FOREIGN KEY (deptid) REFERENCES Dept (ID) --FOREIGN key name foreign key reference table ( Reference field ) )

Note:

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

--1.6 FOREIGN KEY constraint -- Employee table CREATE TABLE Employee ( ID INT PRIMARY KEY, EmpName VARCHAR (20), Deptname VARCHAR-- department name ) INSERT into employee VALUES (1, ' Zhang San ', ' Software Development Department '); INSERT into Employee VALUES (2, ' John Doe ', ' Software Development Department '); INSERT into Employee VALUES (3, ' Harry ', ' Application Maintenance Department '); SELECT * from employee; -- Add employee, Department name data redundancy is high INSERT into Employee VALUES (4, ' Chen vi ', ' Software Development Department '); -- Solving the problem of high data redundancy: placing redundant fields in a separate table -- independent design of a departmental table CREATE TABLE Dept ( ID INT PRIMARY KEY, Deptname VARCHAR (20) ) DROP TABLE employee; -- Modify employee table CREATE TABLE Employee ( ID INT PRIMARY KEY, EmpName VARCHAR (20), DeptID INT,-- Change Department name to department ID -- declaring a foreign key constraint CONSTRAINT emlyee_dept_fk FOREIGN KEY (deptid) REFERENCES Dept (ID) on UPDATE CASCADE on DELETE CASCADE--on CASCADE updat E : Cascading modifications --FOREIGN key name foreign key reference table ( Reference field ) ) INSERT into Dept (id,deptname) VALUES (1, ' Software Development Department '); INSERT into Dept (Id,deptname) VALUES (2, ' Application Maintenance Department '); INSERT into Dept (Id,deptname) VALUES (3, ' Secretary '); INSERT into employee VALUES (1, ' Zhang San ', 1); INSERT into Employee VALUES (2, ' John Doe ', 1); INSERT into Employee VALUES (3, ' Harry ', 2); INSERT into Employee VALUES (4, ' Chen vi ', 3); -- problem : The record is not legal in business, employees insert a non-existent department data INSERT into employee VALUES (5, ' Chen vi ', 4);-- violation of FOREIGN KEY constraint: cannot add or update a child row:a Forei GN key Constraint fails (' DAY16 ', ' Employee ', constraint ' EMLYEE_DEPT_FK ' FOREIGN key (' DeptID ') REFERENCES ' dept ' (' ID ')) /c3> --1) When you have a FOREIGN key constraint, add the order of the data: Add the primary table first, and then add the secondary table data --2) When you have a FOREIGN key constraint, modify the order of the data: Modify the secondary table first, and then modify the main table data --3) When you have a foreign key constraint, delete the order of the data: delete the secondary table before deleting the main table data - -Modify the Department ( cannot directly modify the main Table ) UPDATE Dept SET id=4 WHERE id=3; -- Modify the employee table first UPDATE employee SET deptid=2 WHERE id=4; -- Delete Department DELETE from dept WHERE id=2; -- Delete the employee table first DELETE from employee WHERE deptid=2; SELECT * FROM Dept; SELECT * from employee;

2.8 cascading operations

Questions: when there's a FOREIGN KEY constraint , you must modify or delete all the associated data in the secondary table before you can modify or delete the main table! However, we want to directly modify or delete the main table data, thereby affecting the secondary table data. Can be implemented with cascading operations!!!

Cascade Modifications: On UPDATE CASCADE

Cascade Delete: On DELETE CASCADE

CREATE TABLE employee ( ID INT PRIMARY KEY, EmpName VARCHAR (2 0), DeptID INT,-- Change department name to department id -- Declaring a foreign KEY constraint CONSTRAINT emlyee_dept_fk FOREIGN key (DeptID) REFERENCES Dept (ID) on UPDATE CASCADE on DELETE cascade  --on CASCADE UPDATE : Cascade Modify --           Foreign key name                   foreign key                reference table ( reference field ) Note: Cascading operations must be used on a foreign key basis

-- Cascade Modification (Modification) -- Direct modification of the Department UPDATE Dept SET id=5 WHERE id=4; -- Cascade Delete -- Delete the department directly DELETE from dept WHERE Id=1;

Database strengthening chapter (1)