SQL基础知识:约束(实例)
作者:丶平凡世界
来源:SQL数据库开发
今天,给大家介绍SQL中六种工作中经常使用到的约束,供大家参考!
NOT NULL约束
NOT NULL 约束强制列不接受 NULL 值。
NOT NULL 约束强制字段始终包含值。这意味着,如果不向字段添加值,就无法插入新记录或者更新记录。
下面的 SQL 强制表"Customers" 的"客户ID" 列和 "姓名" 列不接受 NULL 值:
CREATE TABLE Customers( 客户ID INT NOT NULL, 姓名 VARCHAR(10) NOT NULL, 地址 VARCHAR(50) NULL, 城市 VARCHAR(20) NULL, 邮编 CHAR(6) NULL, 省份 VARCHAR(20) NULL ) ;
一旦这两列有空值NULL被插入,系统就会报错提示,例如我们插入如下信息:
INSERT INTO dbo.Customers ( 姓名, 地址, 城市, 邮编, 省份 ) VALUES (NULL,'花城大道1号','广州市','51000',NULL)
结果:
UNIQUE约束
UNIQUE 约束唯一标识数据库表中的每条记录,和 PRIMARY KEY 约束均为列或列集合提供了唯一性的保证,PRIMARY KEY 约束拥有自动定义的 UNIQUE约束。
请注意,每个表可以有多个 UNIQUE 约束,但是每个表只能有一个 PRIMARY KEY 约束。
CREATE TABLE 时的 SQL UNIQUE 约束
下面的 SQL 在 "Orders" 表创建时在 "订单ID" 列上创建 UNIQUE 约束:
MySQL:
CREATE TABLE dbo.Orders( 订单ID INT NOT NULL, 客户ID INT NULL, 员工ID INT NULL, 订单日期 DATETIME NULL, 发货ID INT NULL, UNIQUE (订单ID) ) ;
SQL Server / Oracle / MS Access:
CREATE TABLE dbo.Orders( 订单ID INT NOT NULL UNIQUE, 客户ID INT NULL, 员工ID INT NULL, 订单日期 DATETIME NULL, 发货ID INT NULL ) ;
唯一约束是被约束的列在插入新数据时,如果和已经存在的列有相同的值,则会报错。
INSERT INTO dbo.Orders ( 订单ID, 客户ID, 员工ID, 订单日期, 发货ID ) VALUES ( 1001,1,2,'2018-11-21 19:21:32',1), ( 1001,2,3,'2018-11-22 11:22:32',5)
结果为:
如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE dbo.Orders( 订单ID INT NOT NULL , 客户ID INT NULL, 员工ID INT NULL, 订单日期 DATETIME NULL, 发货ID INT NULL, CONSTRAINT uc_OrderID UNIQUE (订单ID,发货ID) ) ;
ALTER TABLE 时的 UNIQUE 约束
当表已被创建时,如需在 "订单ID" 列创建 UNIQUE 约束,请使用下面的 SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD UNIQUE (订单ID)
如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CONSTRAINT uc_CustomerID UNIQUE (客户ID,姓名)
删除UNIQUE 约束
如需删除UNIQUE 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Orders DROP INDEX uc_OrderID
SQL Server / Oracle / MS Access:
ALTER TABLE Customers DROP CONSTRAINT uc_CustomerID
PRIMARY KEY约束
PRIMARY KEY 约束唯一标识数据库表中的每条记录,主键必须包含唯一的值,主键列不能包含 NULL 值,每个表都应该有一个主键,并且每个表只能有一个主键。
CREATE TABLE 时的 PRIMARY KEY 约束
下面的 SQL 在 "Customers" 表创建时在 "客户ID" 列上创建 PRIMARY KEY 约束:
MySQL:
CREATE TABLE dbo.Customers( 客户ID INT NOT NULL, 姓名 VARCHAR(10) NULL, 地址 VARCHAR(50) NULL, 城市 VARCHAR(20) NULL, 邮编 CHAR(6) NULL, 省份 VARCHAR(20) NULL, PRIMARY KEY (客户ID) ) ;
SQL Server / Oracle / MS Access:
CREATE TABLE dbo.Customers( 客户ID INT NOT NULL PRIMARY KEY, 姓名 VARCHAR(10) NULL, 地址 VARCHAR(50) NULL, 城市 VARCHAR(20) NULL, 邮编 CHAR(6) NULL, 省份 VARCHAR(20) NULL ) ;
如需命名 PRIMARY KEY 约束,并定义多个列的 PRIMARY KEY 约束,请使用下面的 SQL 语法:MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE dbo.Customers( 客户ID INT NOT NULL PRIMARY KEY, 姓名 VARCHAR(10) NULL, 地址 VARCHAR(50) NULL, 城市 VARCHAR(20) NULL, 邮编 CHAR(6) NULL, 省份 VARCHAR(20) NULL, CONSTRAINT pk_CustomerID PRIMARY KEY (客户ID,姓名) ) ;
注释:在上面的实例中,只有一个主键 PRIMARY KEY(pk_CustomerID)。然而,pk_CustomerID 的值是由两个列(客户ID和姓名)组成的。
ALTER TABLE 时的 PRIMARY KEY 约束
当表已被创建时,如需在 "客户ID" 列创建 PRIMARY KEY 约束,请使用下面的 SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD PRIMARY KEY (客户ID)
如需命名 PRIMARY KEY 约束,并定义多个列的 PRIMARY KEY 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CONSTRAINT pk_CustomerID PRIMARY KEY (客户ID,姓名)
注释:如果您使用 ALTER TABLE 语句添加主键,必须把主键列声明为不包含 NULL 值(在表首次创建时)。
删除 PRIMARY KEY 约束
如需删除 PRIMARY KEY 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Customers DROP PRIMARY KEY
SQL Server / Oracle / MS Access:
ALTER TABLE Customers DROP CONSTRAINT pk_CustomerID
FOREIGN KEY约束
一个表中的 FOREIGN KEY 指向另一个表中的 PRIMARY KEY,让我们通过一个实例来解释外键。请看下面两个表:
"Customers" 表:
"Orders" 表:
请注意,"Orders" 表中的 "客户ID" 列指向 "Customers" 表中的 "客户ID" 列。
"Customers" 表中的 "客户ID" 列是 "Customers" 表中的 PRIMARY KEY。
"Orders" 表中的 "客户ID" 列是 "Orders" 表中的 FOREIGN KEY。
FOREIGN KEY 约束用于预防破坏表之间连接的行为。
FOREIGN KEY 约束也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
CREATE TABLE 时的FOREIGN KEY 约束
下面的 SQL 在 "Orders" 表创建时在 "客户ID" 列上创建 FOREIGN KEY 约束:
MySQL:
CREATE TABLE Orders ( 订单ID INT NOT NULL, 客户ID INT NULL, 员工ID INT NULL, 订单日期 DATETIME NULL, 发货ID INT NULL, PRIMARY KEY (订单ID), FOREIGN KEY (客户ID) REFERENCES Customers(客户ID) )
SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( 订单ID INT NOT NULL PRIMARY KEY, 客户ID INT NULL, 员工ID INT NULL, 订单日期 DATETIME NULL, 发货ID INT NULL, FOREIGN KEY (客户ID) REFERENCES Customers(客户ID) )
如需命名 FOREIGN KEY 约束,并定义多个列的 FOREIGN KEY 约束,请使用下面的 SQL 语法:MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders ( 订单ID INT NOT NULL, 客户ID INT NULL, 员工ID INT NULL, 订单日期 DATETIME NULL, 发货ID INT NULL, PRIMARY KEY (订单ID), CONSTRAINT fk_CusOrders FOREIGN KEY (客户ID) REFERENCES Customers(客户ID) )
ALTER TABLE 时的FOREIGN KEY 约束
当 "Orders" 表已被创建时,如需在 "客户ID" 列创建 FOREIGN KEY 约束,请使用下面的 SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD FOREIGN KEY (客户ID) REFERENCES Customers(客户ID)
如需命名 FOREIGN KEY 约束,并定义多个列的 FOREIGN KEY 约束,请使用下面的 SQL 语法:MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders ADD CONSTRAINT fk_CusOrders FOREIGN KEY (客户ID) REFERENCES Persons(客户ID)
删除 FOREIGN KEY 约束
如需删除FOREIGN KEY 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Orders DROP FOREIGN KEY fk_CusOrders
SQL Server / Oracle / MS Access:
ALTER TABLE Orders DROP CONSTRAINT fk_CusOrders
DEFAULT约束
DEFAULT 约束用于向列中插入默认值。
如果没有规定其他的值,那么会将默认值添加到所有的新记录。
CREATE TABLE 时的DEFAULT 约束
下面的 SQL 在 "Customers" 表创建时在 "城市" 列上创建 DEFAULT 约束:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE dbo.Customers( 客户ID INT NOT NULL, 姓名 VARCHAR(10) NULL, 地址 VARCHAR(50) NULL, 城市 VARCHAR(20) NULL DEFAULT '北京市', 邮编 CHAR(6) NULL, 省份 VARCHAR(20) NULL ) ;
SQL Server中通过使用类似 GETDATE() 这样的函数,DEFAULT 约束也可以用于插入系统值:
CREATE TABLE dbo.Orders( 订单ID INT NOT NULL, 客户ID INT NULL, 员工ID INT NULL, 订单日期 DATETIME NULL DEFAULT GETDATE(), 发货ID INT NULL ) ;
ALTER TABLE 时的DEFAULT 约束
当表已被创建时,如需在 "城市" 列创建 DEFAULT 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Customers ALTER 城市 SET DEFAULT '北京市'
SQL Server / MS Access:
ALTER TABLE Customers ADD CONSTRAINT DF_Customers DEFAULT('北京市') FOR 城市
--注释
--Customers为表名
--城市 为列名
--DF_Customers 为我们创建的默认约束的名称 约束名称一般为:约束类型简称_表名
Oracle:
ALTER TABLE Customers MODIFY 城市 DEFAULT '北京市'
删除DEFAULT 约束
如需删除DEFAULT 约束,请使用下面的 SQL:
MySQL:
ALTER TABLE Customers ALTER 城市 DROP DEFAULT
SQL Server / Oracle / MS Access:
ALTER TABLE Customers ALTER COLUMN 城市 DROP DEFAULT
CHECK约束
CHECK 约束用于限制列中的值的范围,如果对单个列定义 CHECK 约束,那么该列只允许特定的值。如果对一个表定义 CHECK 约束,那么此约束会基于行中其他列的值在特定的列中对值进行限制。
CREATE TABLE 时的CHECK 约束
下面的 SQL 在 "Customers" 表创建时在 "客户ID" 列上创建 CHECK 约束。CHECK 约束规定 "客户ID" 列必须只包含大于 0 的整数。
MySQL:
CREATE TABLE dbo.Customers( 客户ID INT NOT NULL, 姓名 VARCHAR(10) NULL, 地址 VARCHAR(50) NULL, 城市 VARCHAR(20) NULL, 邮编 CHAR(6) NULL, 省份 VARCHAR(20) NULL, CHECK (客户ID>0) ) ;
SQL Server / Oracle / MS Access:
CREATE TABLE dbo.Customers( 客户ID INT NOT NULL CHECK (客户ID>0), 姓名 VARCHAR(10) NULL, 地址 VARCHAR(50) NULL, 城市 VARCHAR(20) NULL, 邮编 CHAR(6) NULL, 省份 VARCHAR(20) NULL ) ;
如需命名 CHECK 约束,并定义多个列的 CHECK 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE dbo.Customers( 客户ID INT NOT NULL, 姓名 VARCHAR(10) NULL, 地址 VARCHAR(50) NULL, 城市 VARCHAR(20) NULL, 邮编 CHAR(6) NULL, 省份 VARCHAR(20) NULL, CONSTRAINT chk_Customers CHECK (客户ID>0 AND 城市='北京市') ) ;
ALTER TABLE 时的CHECK 约束
当表已被创建时,如需在 "客户ID" 列创建 CHECK 约束,请使用下面的 SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CHECK (客户ID>0)
如需命名 CHECK 约束,并定义多个列的 CHECK 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Customers ADD CONSTRAINT chk_Customers CHECK (客户ID>0 AND 城市='北京市')
删除CHECK 约束
如需CHECK 约束,请使用下面的 SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Customers DROP CONSTRAINT chk_Customers
MySQL:
ALTER TABLE Customers DROP CHECK chk_Customers
批注
以上六种约束是工作中经常使用到的,主要还是用来规范数据,随着数据量的增多,如果不对表结构加以约束,那么会有越来越多的“脏数据”进入到数据库,这对业务系统来说是非常不愿意碰到的。所以为了能够高效的使用数据库,请从表结构的设计上下更多的功夫。