DAY15 (MySQL's fragmentary knowledge)

Data integrity

　　Entity integrity

Entity: A row in a table (a row of records) instead of an entity

The role of entity integrity: identifies each row of data that is not duplicated.

Constraint type: PRIMARY KEY constraint, UNIQUE constraint, auto-Grow column.

PRIMARY KEY constraint: Identifies the column as unique, NOT NULL.

Note: You must have a primary key in each table

Three ways to add a primary key:

1. Created directly in the process of creating a table.

CREATE TABLE student (ID int primary key,name varchar (50));

2. Special creation in creating tables: The advantage is that you can federate a primary key

CREATE TABLE student (ID int,name varchar (), primary key (ID)); CREATE TABLE Student (classid int,stuid int,name varchar (), primary key (Classid,stuid));

3. Create a primary key for the column after the table creation is complete

Alter  table  name add  primary key (column name);

　　

　　

Unique constraint; The value that identifies the column cannot be duplicated.

　　　　

CREATE TABLE Student (Id int primary key,name varchar () unique);

Self-growth: The data that identifies the column automatically grows. There is no need to give the value artificially.

SQL Server database (identity)  Oracle Database (sequence) adds an autogrow value to the primary key, and the column can only be an integer type Create TABLE student (Id int primary key auto_ Increment,name varchar); INSERT into student (Name) VALUES (' Tom ');

　　

Domain integrity

Action: Restricts the data in the cell to be correct and does not compare to other cells in this column

The field represents the current cell;

Constraints on domain integrity: data type DEFAULT constraint (defaults) non-null constraint (NOT NULL) CHECK constraint (MySQL not supported)

Default value constraint:

CREATE TABLE Student (Id int pirmary key,name varchar () not null,sex varchar (ten) Default ' Male ');

INSERT into student1 values (1, ' Tom ', ' female ');

INSERT into student1 values (2, ' Jerry ', default);

　　　　　　Non-empty constraint: notnull

CREATE TABLE Student (Id int pirmary key,name varchar () Not null,//This column is represented as non-empty sex varchar); INSERT into student values (1, ' Tom ', NULL);

Referential integrity (referential integrity)

FOREIGN key: FOREIGN key

Methods for creating foreign keys

ALTER table name ADD CONSTRAINT PRIMARY Key name FOREIGN key (column name) REFERENCES table name (column name);

The relationship between tables and tables;

One

A pair of more;

Many-to-many;

　　

DAY15 (MySQL's fragmentary knowledge)