MySQL integrity constraints

Integrity constraints

Data integrity is to ensure that the data inserted into the data is correct, which prevents the user from possible input errors.

2.1 Entity (record) integrity

Specifies that a row of a table (that is, each record) is the only entity in the table. Entity integrity is implemented through the primary key of the table.

Primary key: cannot be null;

Create a table T1, and declare a primary key

One: (Only one field can be specified as the primary key)

CREATE TABLE T1 (

ID int PRIMARY KEY,

NAME varchar (100)

);

Notation Two: (Specify the Federated primary key)

CREATE TABLE T2 (

ID int,

NAME varchar (100),

Primary KEY (ID)

);

Three: (Create a table without any constraints, and finally modify the constraint) recommendations

CREATE TABLE T3 (

ID int,

NAME varchar (100)

);

ALTER TABLE T3 add primary key (ID);

Attention:

Logical primary KEY (recommended): In addition to uniquely identifying a record, there is no other meaning. General name ID

Business PRIMARY Key: There is a certain business significance.

Mysql:

CREATE TABLE T4 (

ID int primary Key auto_increment, #自动增长, is not recommended. Database migration is inconvenient, not all databases support autogrow, such as Oracle does not support.

NAME varchar (100)

);

2.2 Domain (field) integrity

A column (that is, a field) of a database table must conform to a specific data type or constraint

1. Type of constraint data: Strong type

2, non-null constraint: NOT NULL

3. Unique constraint: Unique

CREATE TABLE T5 (

ID int primary Key auto_increment,

USERNAME varchar (+) is not null unique,

Phone_num varchar (one) unique,

GENDER varchar (TEN) NOT NULL

);

2.3 Referential integrity (multi-table design): Define foreign keys

Note: The Relationship of class and table structure, the relationship between objects and records (Help learning Orm:hibernate, MyBatis)

Orm:object Relation Mapping (Java Object-oriented DB relational database mapping)

MySQL integrity constraints