SQL 笔记系列四(约束)

2018-12-03 本文已影响0人 _发强

前言

SQL 约束用于规定表中的字段数据规则。就是为字段添加一些限制要求。如果在操作字段时，数据格式不符合所规定的限制条件，则该操作行为会停止。

相关约束

auto increment

主键自增，一般用于表的 Id 字段，id 默认起始值为 1 ，后续插入的数据，则自动+1，
示例

create table Name(
id  int not null auto_increment,
name varchar(25) not null
....
)

insert into Name(name) values('发强')  // 执行完成后， 该条数据 id  = 1 ,再次执行，则 id 自动+1 

// 如果需要auto_increment 序列以其他的值起始，
alter table Name auto_increment=100

not null

标明某列不能存储 Null 值

create table User(
id int not null
...
)

修改表字段的 null 约束

alter table [tableName] modify column_name null;   -- 修改字段可为null.
alter table [tableName] modify column_name not null; -- 修改字段不可为 null.

primary key (主键)

primary key 约束唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL 值。
每个表都应该有一个主键，并且每个表只能有一个主键。

示例一
创建表时，指定 P_id 为主键

写法一：

CREATE TABLE Persons(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)

写法二：

CREATE TABLE Persons(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
)

示例二
如需命名 PRIMARY KEY 约束，并定义多个列的 PRIMARY KEY 约束，请使用下面的 SQL 语法

create table Persons(
P_Id int not null ,
LastName varchar(255) not null,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
constraint pk_PersonID PRIMARY KEY (P_Id,LastName)
)

在上面的实例中，只有一个主键 PRIMARY KEY（pk_PersonID）。然而，pk_PersonID 的值是由两个列（P_Id 和 LastName）组成的

添加主键
当表已经创建之后，需要添加主键操作。

-- 添加单个属性为主键
alter table Persons add primary key (P_Id)
-- 添加多个属性为主键 -- [主键名称] 这里不需要是
alter table [表名] constraint [主键名称] primary key (column_name1 , column_name2, ...)

撤销主键约束

alter table [TableName] drop primary key
or
alter table [tableName] drop constraint [主键名称]

unique

保证某列的每行必须是唯一的值，也就是标明某个字段不可重复。
unique 和 primary key 的区别：

都标明的是当前列、列集合不可重复。
primary key 拥有自动定义的 unique。个人理解为 primary key 约束里面自动调用了 unique
每个表中可以有多个 unique , 但是只能有一个 primary key .

语法

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
)

>>更多用法

foreign key

一个表中的 FOREIGN KEY 指向另一个表中的 UNIQUE KEY(唯一约束的键)。
语法

create Table TableName(
id int not null primary key,
name varchar(255),
u_id int ,
foreign key (column_name(s))
references OtherTable(column_names)

关键字 ..foreign key .. references ..
示例

create Table Orders(
id int not null auto_increment primary key ,
order_no varchar(25) not null ,
u_id int ,
[constraint fk_names -- 定义别名(alias) 可忽略]
foreign key (u_id)
references User(id)

创建一个 Orders 表，使用 foreign key 约束 u_id 指向 User 表中的 id ，在插入数据是，如果 Orders u_id 插入一个 User 中不存在的 id , 则插入异常。

修改 foreign key

alter table Orders
add foreign key (u_id)
references User(id)

撤销 foreign key

alter table Orders drop foreign key u_id

CHECK

check 约束用于限制列中的值的范围，也就是在插入数据时，需要满足某些条件。
语法

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
 // 添加多列约束语句： constraint ck_person CHECK (P_Id > 0 and City='HangZhou')
)

插入的数据 P_Id 必须 > 0
修改 check 约束

// 添加单个列 约束
alter table Persons add check (P_Id > 0)

// 添加多列约束
alter table Persons add constraint ck_person check (P_Id > 0 and City='HangZhou')

撤销 check 约束

alter table Persons drop check P_Id  // ck_person

DEFAULT

default 用于向列中插入默认值，当向某一个表中插入部分数据时，为赋值的列如果有设置 default , 则会有默认值。未设置 default 则为 null .
示例

// 先创建一个 NewTable 表
create Table NewTable(
id int not null auto_increment primary key ,
name varchar(20) not null,
age int default 18
)

// 插入数据
insert into NewTable(id , name) values(1,'发强')
-- 该语句执行后， age 字段默认为 18 .

// 添加 default 约束
alter table NewTable alter name set default '张三'

// 删除 default 约束
alter table NewTable alter name drop default

Null

null 值和 0 ， " " 是不等值的。null 表示的是什么都没有。

is null
在 sql 语句中如果我们需要在条件中判断是否是 null , 就需要使用 is null 来判断，而不能使用 > 、< 或 <>。
示例

select * from User where address is null

is not null
反之，如果我们需要筛选非 null 数据时，则需要使用 is not null.
示例

select * from User where address is not null

关于 null 处理，后面还有一些 ISNULL()、NVL()、IFNULL() 和 COALESCE() 函数介绍