MySQL完整性限制

資料完整性限制是在表和字段上強制執行的資料檢驗規則，為了防止不規範的資料進入資料庫，在使用者對資料進行插入、修改、删除等操作時，DBMS自動按照一定的限制條件對資料進行監測，主要是對空值和重複值的限制，使不符合規範的資料不能進入資料庫，以保證資料存儲的完整性和準确性。

完整性分類

按照限制的不同類型可以分為實體完整性、域完整性、參照完整性、使用者自定義完整性。

實體完整性

用來辨別表中的每一條記錄，一條記錄代表一個實體(entity)。

主鍵限制(primary key)

每個表中隻能有一個主鍵。
主鍵值須非空不重複。
可設定單字段主鍵，也可設定多字段聯合主鍵。
聯合主鍵中多個字段的資料完全相同時，才違反主鍵限制。

列級添加主鍵限制：

creat table <表名> (
<字段名1> <字段類型1> primary key，
<字段名2> <字段類型2>，
……
<字段名n> <字段類型n>);

示例：

mysql> create table employee(
    -> e_id int primary key,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int);

表級添加主鍵限制：

create table <表名>(
<字段名1> <字段類型1>,
<字段名2> <字段類型2>,
......
<字段名n> <字段類型n>,
[constraint 主鍵限制名] primary key(字段名1[,字段名2,...字段名n]));

示例：

mysql> create table employee(
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int,
    -> primary key(e_name,e_sex));

已有表添加主鍵限制：

alter table <表名> add [constraint 主鍵限制名] primary key(字段名);

示例：

-- 建立無主鍵限制的表
mysql> create table employee(
	-> e_id int,
	-> e_name varchar(5),
	-> e_sex varchar(5),
	-> e_age int,
	-> d_id int);
-- 添加主鍵限制
mysql> alter table employee add primary key(e_id);

删除主鍵限制：

alter table <表名> drop primary key;

示例：

alter table employee drop primary key;

唯一限制(unique)

指定列的資料不能重複。
可以為空，但隻能出現一個空值。

列級添加唯一限制：

creat table <表名> (
<字段名1> <字段類型1> unique,
<字段名2> <字段類型2>,
……
<字段名n> <字段類型n>);

示例：

mysql> create table employee(
	-> e_id int,
	-> e_name varchar(5) unique,
	-> e_sex varchar(5),
	-> e_age int,
	-> d_id int);

表級添加唯一限制：

creat table <表名> (
<字段名1> <字段類型1>,
<字段名2> <字段類型2>,
……
<字段名n> <字段類型n>,
[constraint 唯一限制名] unique (字段名1[,字段名2...字段名n]));

示例：

mysql> create table employee(
	-> e_id int,
	-> e_name varchar(5),
	-> e_sex varchar(5),
	-> e_age int,
	-> d_id int,
	-> unique (e_name,e_sex));

已有表添加唯一限制：

alter table <表名> add [constraint 唯一限制名] unique (字段名);

示例：

-- 建立表
mysql> create table employee(
	-> e_id int,
	-> e_name varchar(5),
	-> e_sex varchar(5),
	-> e_age int,
	-> d_id int);
-- 添加唯一限制
mysql> alter table employee add unique(e_name);

删除唯一限制：

alter table <表名> drop index <唯一限制名>;

如果單個字段沒有指定唯一限制名，則預設的唯一限制名為字段名。如果是多個字段組合為唯一限制時候，預設的唯一限制名為第一個字段的名稱。如果指定了限制名，則删除的時候寫限制名。

示例：

alter table employee drop index e_name;

自動增長列(auto_increment)

指定字段的資料自動增長。
配合主鍵一起使用，并且隻适用于整數類型。
預設從1開始，每增加一條記錄，該字段的值會增加1。
即使資料删除，還是從删除的序号繼續增長。

建立自動增長限制：

creat table <表名> (
<字段名1> <字段類型1> primary key auto_increment,
<字段名2> <字段類型2>,
……
<字段名n> <字段類型n>);

示例：

mysql> create table employee(
    -> e_id int primary key auto_increment,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int);

已有表添加自動增長限制：

alter table <表名> modify <字段名> <字段類型> auto_increment;

示例：

-- 建立無自增限制的表
mysql> create table employee(
    -> e_id int primary key,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int);
-- 添加自增限制
alter table employee modify e_id int auto_increment;

設定自動增長初始值：

alter table <表名> auto_increment=初始值;

示例：

alter table employee auto_increment=1000;

删除自增限制：

alter table <表名> modify <字段名> <字段類型>;

示例：

alter table employee modify e_id int;

域完整性

域完整性是針對單元格的限制，不與其它行參考比較。

非空限制(not null)

字段的值不能為空。

建立非空限制：

creat table <表名> (
<字段名1> <字段類型1> not null,
<字段名2> <字段類型2>,
……
<字段名n> <字段類型n>);

示例：

mysql> create table employee(
    -> e_id int,
    -> e_name varchar(5) not null,
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int);

已有表添加非空限制：

alter table <表名> modify <字段名> <字段類型> not null;

示例：

-- 建立表
mysql> create table employee(
    -> e_id int,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int);
-- 添加非空限制
alter table employee modify e_name int not null;

删除非空限制：

alter table <表名> modify <字段名> <字段類型> [null];

示例：

alter table employee modify e_name int;

預設限制(default)

如果新插入一條記錄時沒有為該字段指派，系統會自動為這個字段指派為預設限制設定的值。
如果插入的資料為“null”，則不會使用預設值，隻有沒有插入資料時候，才會使用預設值。

建立預設限制：

creat table <表名> (
<字段名1> <字段類型1> default value,
<字段名2> <字段類型2>,
……
<字段名n> <字段類型n>);

示例：

mysql> create table employee(
    -> e_id int,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int default 0,
    -> d_id int);

已有表添加預設限制：

alter table <表名> modify <字段名> <字段類型> default value;

示例：

-- 建立表
mysql> create table employee(
    -> e_id int,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int);
-- 添加預設限制
alter table employee modify e_age int default 0;

删除預設限制：

alter table <表名> modify <字段名> <字段類型>;

示例：

alter table employee modify e_age int;

參照完整性

參照完整性指多表之間的對應關系，在一張表中執行資料插入、更新、删除等操作時，DBMS都會跟另一張表進行對照，避免不規範的操作，以確定資料存儲的完整性。

外鍵限制(foreign key)

某一表中某字段的值依賴于另一張表中某字段的值
主鍵所在的表為主表，外鍵所在的表為從表
每一個外鍵值必須與另一個表中的主鍵值相對應

建立外鍵限制：

creat table <表名> (
<字段名1> <字段類型1>,
<字段名2> <字段類型2>,
……
<字段名n> <字段類型n>,
[constraint 外鍵限制名] foreign key(字段名) references <主表>(主鍵字段));

示例：

-- 建立一個主表
mysql> create table department(
    -> d_id int primary key,
    -> d_name varchar(5),
    -> d_num int);
-- 建立從表的同時添加外鍵
mysql> create table employee(
    -> e_id int primary key,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int,
    -> foreign key(d_id) references department(d_id));

已有表中添加外鍵限制：

alter table <表名> add [constraint 外鍵限制名] foreign key(字段名) references <主表>(主鍵字段);

示例：

-- 建立一個主表
mysql> create table department(
    -> d_id int primary key,
    -> d_name varchar(5),
    -> d_num int);
-- 建立從表
mysql> create table employee(
    -> e_id int primary key,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int);
-- 添加外鍵限制
mysql> alter table employee add constraint fk_d_id foreign key(d_id) references department(d_id));

在有外鍵限制的表中導入資料時，如果資料不完整就會觸發外鍵限制，這種情況下可以使用FOREIGN_KEY_CHECKS設定是否檢查外鍵限制。

set FOREIGN_KEY_CHECKS=0; #在導入前設定為不檢查外鍵限制
set FOREIGN_KEY_CHECKS=1; #在導入後恢複檢查外鍵限制

删除外鍵限制：

alter table <表名> drop foreign key <外鍵限制名>;

示例：

alter table employee drop foreign key fk_d_id;

先删除從表再删除主表。
先删除外鍵限制，再删除表。

使用者自定義完整性

根據使用者實際的完整性要求來定義，在執行資料插入、更新等操作時，DBMS會檢查資料是否滿足檢查限制中的限定條件，避免不符合條件的操作，以保證資料存儲的準确性。

檢查限制(check)

指定需要檢查的限定條件。

建立檢查限制：

creat table <表名> (
<字段名1> <字段類型1>,
<字段名2> <字段類型2>,
……
<字段名n> <字段類型n>,
check(<限定條件>));

示例：

mysql> create table employee(
    -> e_id int primary key,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int,
    -> check(e_age>=0));

已有表添加檢查限制：

alter table <表名> add constraint <檢查限制名> check(<限定條件>);

示例：

-- 建立表
mysql> create table employee(
    -> e_id int primary key,
    -> e_name varchar(5),
    -> e_sex varchar(5),
    -> e_age int,
    -> d_id int);
-- 添加檢查限制
mysql> alter table employee add constraint ch_e_age check(e_age>=0));

删除檢查限制：

alter table <表名> drop constraint <檢查限制名>;

示例：

mysql> alter table employee drop constraint ch_e_age;

MySQL完整性限制

完整性分類

實體完整性

域完整性

參照完整性

使用者自定義完整性

繼續閱讀

報錯：'mysql' 不是内部或外部指令，也不是可運作的程式或批處理檔案。

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

Linxu常用指令技巧彙總

ERROR 1 (HY000): Can't create/write to file '/tmp/#sql_4188_1.MYI' (Errcode: 28)

艱難安裝LDAP,SSL認證

《Linux指令行與Shell腳本程式設計大全第2版.布盧姆》pdf

MySQL的4種隔離級别？出現問題

XX系統實施過程問題總結

無元件上傳圖檔到資料庫中，最完整解決方案

【MySQL資料庫】資料庫索引事務1.索引2.事務

neo4j之cypher使用文檔

NOSQL安全攻擊

mybatis_入門程式Mybatis入門

mysql使用source指令導入.sql檔案

登入plsql 報錯 the account is locked --使用者被鎖

SequoiaDB巨杉資料庫C++驅動概述