Database integrity and constraints

Database integrity and constraints 1. database integrity 1. Defining link integrity is a constraint or rule proposed for the relational model to ensure the correctness and compatibility of data in the database. Integrity includes: domain Integrity: domain integrity ensures the rationality of database Field Values. Including the restriction type (data type), format (by checking constraints and Rules ),

Database integrity and constraints 1. database integrity 1. Defining link integrity is a constraint or rule proposed for the relational model to ensure the correctness and compatibility of data in the database. Integrity includes: domain Integrity: domain integrity ensures the rationality of database Field Values. Including the restriction type (data type), format (by checking constraints and Rules ),

Database integrity and constraints

I. database integrity

1. Definition

To ensure the correctness and compatibility of data in the database, Relational integrity is a constraint or rule proposed for the relational model. Integrity includes:

Domain integrity:

Domain integrity ensures the rationality of database Field Values. Including restriction type (data type), format (by checking constraints and rules), possible value range (by foreign key constraints, check constraints, default value definitions, non-empty constraints and Rules ), in today's relational DBMS, domain integrity constraints are generally checked.

Entity integrity:

Entity integrity means that the primary Keywords of a link cannot be repeated or take null values,

Integrity of reference:

Also known as "Integrity of reference", it is a constraint that defines the relationship between the master keyword and external keyword reference. When deleting and inputting records, the reference integrity maintains the defined relationship between the tables. integrity of the reference to ensure that the key values are consistent in all tables. such consistency requires that a nonexistent value cannot be referenced. if a key value is changed, the reference to the key value must be changed in the whole database.

Custom integrity:

Entity integrity and referential integrity are applicable to any relational database system. They are mainly constraints that must be effective for the values of primary and external Keywords of a link. User-Defined integrity sets constraints on the data involved in a specific application based on the requirements of the application environment and actual needs. This constraint mechanism should not be provided by the application, but should be defined and verified by the relational model. User-Defined integrity mainly includes the field validity constraint and record validity.

2. Specific embodiment

Entity Integrity Constraint (primary key -- unique and non-empty) primary key (); default processing: No action (Execution denied)

Reference integrity constraints (foreign key constraints) foregin key () references tableName (filedName) [on delete | update casecade | no action]; default handling: cascade update or reject execution

User-Defined integrity constraints (not null, unique, check phrase); default handling: No action (Execution denied)

Method 1: 1 # example: 2 create table a1 (3 id INT (11) not null primary key auto_increment, # a primary key definition method 4 name VARCHAR (20) not null, 5 type INT (2) not null default 0, 6 sex char (2) null default 'male' CHECK (sex = 'male' OR sex = 'female ') 7) ENGINE = innodb default charset = utf88 9 # id, primary key is NOT empty auto-increment mode 2: # example create table a2 (id INT (11) not null primary key auto_increment, a_id int not null references a1 (id), t_id int not NULL unique, foreign key (a_id) references a1 (id) on delete cascade on update cascade) ENGINE = innodb default charset = utf8 auto_increment = 1; Method 3: # create table a3 instance (id int not null auto_increment, get_id int not null default 0, set_id int not null unique default 0, title char (50) null, primary key (id), # Another method for defining the primary key foreign key (get_id) references a1 (id) on delete cascade on update cascade, foreign key (set_id) references a2 (id) on delete cascade on update cascade) engine = innodb default charset = utf8 auto_increment = 1;