SQL
约束用于规定表中的数据规则。
如果存在违反约束的数据行为,行为会被约束终止。
约束可以在创建表时规定(通过 CREATE TABLE
语句),或者在表创建之后规定(通过 ALTER TABLE
语句)。
SQL CREATE TABLE + CONSTRAINT
语法:
CREATE TABLE table_name(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);
一、not null
规定某列不能存储 NULL
值。如果不给该字段添加值,就无法插入新纪录或者更新记录。
在默认的情况下,表的列接受 NULL
值。
CREATE TABLE Persons(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
二、unique
保证某列的值具有唯一性。
与 PRIMARY KEY
的作用相同。 区别在于每个表可以有多个 UNIQUE
约束,但是每个表只能有一个PRIMARY KEY
约束。
1、CREATE TABLE
时
MySQL
:
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)
)
SQL Server / Oracle / MS Access
:
CREATE TABLE Persons(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
如需命名 UNIQUE 约束,并定义多个列的 UNIQUE 约束,请使用下面的 SQL 语法:
MySQL / SQL Server / Oracle / MS Access
:
CREATE TABLE Persons(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
)
2、ALTER TABLE
时
MySQL / SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
ADD UNIQUE (P_Id)
如需命名 UNIQUE
约束,并定义多个列的 UNIQUE
约束,请使用下面的 SQL
语法:
MySQL / SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
3、撤销 UNIQUE
约束
MySQL
:
ALTER TABLE Persons
DROP INDEX uc_PersonID
SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID
三、primary key
唯一标识数据库表中的每条记录。
主键必须包含唯一的值。
主键列不能包含 NULL
值。
每个表都应该有一个主键,并且每个表只能有一个主键。
1、CREATE TABLE
时
MySQL
:
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)
)
SQL Server / Oracle / MS Access
:
CREATE TABLE Persons(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
如需命名 PRIMARY KEY
约束,并定义多个列的 PRIMARY KEY
约束,请使用下面的 SQL
语法:
MySQL / SQL Server / Oracle / MS Access
:
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)组成的。
2、ALTER TABLE
MySQL / SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
如需命名 PRIMARY KEY
约束,并定义多个列的 PRIMARY KEY
约束,请使用下面的 SQL
语法:
MySQL / SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
注释:
如果您使用 ALTER TABLE
语句添加主键,必须把主键列声明为不包含 NULL
值(在表首次创建时)。
3、撤销
MySQL
:
ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
四、foreign key
一个表中的 FOREIGN KEY
指向另一个表中的 UNIQUE KEY
(唯一约束的键)。
用于预防破坏表之间连接的行为。
也能防止非法数据插入外键列,因为它必须是它指向的那个表中的值之一。
1、CREATE TABLE
时
MySQL
:
CREATE TABLE Orders(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
SQL Server / Oracle / MS Access
:
CREATE TABLE Orders(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
如需命名 FOREIGN KEY
约束,并定义多个列的 FOREIGN KEY
约束,请使用下面的 SQL
语法:
MySQL / SQL Server / Oracle / MS Access
:
CREATE TABLE Orders(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
2、ALTER TABLE
时
MySQL / SQL Server / Oracle / MS Access
:
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
如需命名 FOREIGN KEY
约束,并定义多个列的 FOREIGN KEY
约束,请使用下面的 SQL
语法:
MySQL / SQL Server / Oracle / MS Access
:
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
3、撤销
MySQL
:
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access
:
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders
五、check
用于限制列中的值的范围。
如果对单个列定义 CHECK
约束,那么该列只允许特定的值。
如果对一个表定义CHECK
约束,那么此约束会基于行中其他列的值在特定的列中对值进行限制。
1、CREATE TABLE
时
MySQL
:
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)
)
SQL Server / Oracle / MS Access
:
CREATE TABLE Persons(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
如需命名 CHECK
约束,并定义多个列的 CHECK
约束,请使用下面的 SQL
语法:
MySQL / SQL Server / Oracle / MS Access
:
CREATE TABLE Persons(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
2、ALTER TABLE
时
MySQL / SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
ADD CHECK (P_Id>0)
如需命名 CHECK
约束,并定义多个列的 CHECK
约束,请使用下面的 SQL
语法:
MySQL / SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
3、撤销
MySQL
:
ALTER TABLE Persons
DROP CHECK chk_Person
SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
六、default
规定没有给列赋值时的默认值。 如果没有规定其他的值,那么会将默认值添加到所有的新记录。
1、CREATE TABLE
时
MySQL / SQL Server / Oracle / MS Access
:
CREATE TABLE Persons(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
通过使用类似 GETDATE() 这样的函数,DEFAULT 约束也可以用于插入系统值:
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
)
2、ALTER TABLE
时
MySQL
:
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
SQL Server / MS Access
:
ALTER TABLE Persons
ADD CONSTRAINT ab_c DEFAULT 'SANDNES' for City
Oracle
:
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'
3、撤销
MySQL
:
ALTER TABLE Persons
ALTER City DROP DEFAULT
SQL Server / Oracle / MS Access
:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT
七、AUTO INCREMENT
我们通常希望在每次插入新记录时,自动地创建主键字段的值。
我们可以在表中创建一个 auto-increment
字段。
MySQL
:
CREATE TABLE Persons(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)
MySQL
使用 AUTO_INCREMENT
关键字来执行 auto-increment
任务。
默认地,AUTO_INCREMENT
的开始值是 1
,每条新记录递增 1
。
要让 AUTO_INCREMENT
序列以其他的值起始,请使用下面的 SQL
语法:
ALTER TABLE Persons AUTO_INCREMENT=100
要在 "Persons" 表中插入新记录,我们不必为 "ID" 列规定值(会自动添加一个唯一的值):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')
上面的 SQL
语句会在 "Persons" 表中插入一条新记录。
"ID" 列会被赋予一个唯一的值。"FirstName" 列会被设置为 "Lars","LastName" 列会被设置为 "Monsen"。
SQL Server
:
CREATE TABLE Persons(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MS SQL Server
使用IDENTITY
关键字来执行 auto-increment 任务。
在上面的实例中,IDENTITY
的开始值是 1
,每条新记录递增 1
。
提示:
要规定 "ID" 列以 10 起始且递增 5,请把 identity
改为 IDENTITY(10,5)
。
要在 "Persons" 表中插入新记录,我们不必为 "ID" 列规定值(会自动添加一个唯一的值):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')
上面的 SQL
语句会在 "Persons" 表中插入一条新记录。"ID" 列会被赋予一个唯一的值。"FirstName" 列会被设置为 "Lars","LastName" 列会被设置为 "Monsen"。
Access
:
CREATE TABLE Persons(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
MS Access
使用 AUTOINCREMENT
关键字来执行 auto-increment
任务。
默认地,AUTOINCREMENT
的开始值是 1
,每条新记录递增1
。
提示:
要规定 "ID" 列以 10 起始且递增 5,请把 autoincrement
改为 AUTOINCREMENT(10,5)
。
要在 "Persons" 表中插入新记录,我们不必为 "ID" 列规定值(会自动添加一个唯一的值):
INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')
上面的 SQL
语句会在 "Persons" 表中插入一条新记录。"ID" 列会被赋予一个唯一的值。"FirstName" 列会被设置为 "Lars","LastName" 列会被设置为 "Monsen"。
for Oracle
:
在Oracle
中,代码稍微复杂一点。
您必须通过sequence
对象(该对象生成数字序列)创建 auto-increment
字段。
请使用下面的 CREATE SEQUENC
E 语法:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
上面的代码创建一个名为 seq_person 的 sequence 对象,它以 1 起始且以 1 递增。该对象缓存 10 个值以提高性能。cache 选项规定了为了提高访问速度要存储多少个序列值。
要在 "Persons" 表中插入新记录,我们必须使用 nextval
函数(该函数从 seq_person 序列中取回下一个值):
INSERT INTO Persons (ID,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen')
上面的 SQL 语句会在 "Persons" 表中插入一条新记录。"ID" 列会被赋值为来自 seq_person 序列的下一个数字。"FirstName"列 会被设置为 "Lars","LastName" 列会被设置为 "Monsen"。
参考链接:https://www.runoob.com/sql/sql-tutorial.html