美文网首页
五、SQL–索引/约束③(唯一约束)

五、SQL–索引/约束③(唯一约束)

作者: 小小一颗卤蛋 | 来源:发表于2020-04-22 23:30 被阅读0次

唯一约束又称为UNIQUE约束,它用于防止一个特定的列中两个记录具有一致的值
如:在员工信息表中希望防止两个或者多个人具有相同的身份证号码。

唯一约束分为单字段唯一约束与复合唯一约束两种。

单字段唯一约束:

如果希望一个字段在表中的值是唯一的,那么就可以将唯一约束设置到这个字段上,设置方式:在字段定义后增加UNIQUE。如果是DB2,那么还要同时将NOT NULL约束设置到这个字段上。

如:创建了表T_Person,并且将唯一约束设置到FNumber字段上:

MYSQL、MSSQLServer:
CREATE TABLE T_Person (FNumber VARCHAR(20) UNIQUE,FName VARCHAR(20),FAge INT)
Oracle:
CREATE TABLE T_Person (FNumber VARCHAR2(20) UNIQUE,FName VARCHAR2(20),FAge NUMBER (10))
DB2:
CREATE TABLE T_Person (FNumber VARCHAR(20) NOT NULL UNIQUE,FName VARCHAR(20),FAge INT)

插入初始的一些测试数据:

INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( "1" , "kingchou", 20);
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( "2" , "stef", 22);
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( "3" , "long", 26);
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( "4" , "yangzk", 27);
INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( "5" , "beansoft", 26);

执行完毕后就能在表T_Person中的看到下面的数据:

FNUMBER FNAME FAGE
1 kingchou 20
2 stef 22
3 long 26
4 yangzk 27
5 beansoft 26

接着执行下面的SQL语句进行测试:

INSERT INTO T_Person (FNumber, FName, FAge) VALUES ( "2" , "kitty", 20)

会报错一下信息:

违反了 UNIQUE KEY 约束 "UQ__T_Person__1A14E395"。不能在对象 "dbo.T_Person" 中插入重复键。

将T_Person表删除:(单字段唯一约束知识点结束)
DROP TABLE T_Person

复合唯一约束:

建立在多个字段上的约束,被约束的字段不能同时重复。
如:字段A的值在表中可以重复,字段B的值在表中也可以重复,但是不能存在字段A的值和字段B的值同时重复的记录,用单字段唯一约束就不能做到,此时就需要用复合唯一约束。
如:公司每个部门单独进行工号编号,且每个部门拥有唯一的部门编号,这样每个员工所属的部门编号是可以在表内重复的,而且每个员工的工号也是可以在表内重复的,但是不能存在所属的部门编号和每个员工的工号同时重复的员工。

定义复合唯一约束:定义在所有字段列表之后,语法如下:

CONSTRAINT 约束名UNIQUE(字段1,字段2……字段n)

这里的“字段1,字段2……字段n”为组成约束的多个字段,如果只有一个字段则可以看做是单字段唯一约束定义的另外一种形式。通过这种形式定义的唯一约束由于有一个确定的名称,所以可以很容易的通过这个名字来删除这个约束。

数据准备
创建了表T_Person,并且将在部门编号字段FDepartmentNumber 和工号字段FNumber 上设置复合唯一约束,并且命名为unic_dep_num:

MYSQL、MSSQLServer:
CREATE TABLE T_Person (FNumber VARCHAR(20),FDepartmentNumber VARCHAR(20),FName VARCHAR(20),FAge INT,CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))

Oracle:
CREATE TABLE T_Person (FNumber VARCHAR2(20),FDepartmentNumber VARCHAR(20),FName VARCHAR2(20),FAge NUMBER (10),CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))

DB2:
CREATE TABLE T_Person (FNumber VARCHAR(20) NOT NULL,FDepartmentNumber VARCHAR(20) NOT NULL,FName VARCHAR(20),FAge INT,CONSTRAINT unic_dep_num UNIQUE(FNumber,FDepartmentNumber))

T_Person表中插入初始的一些测试数据:

INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)VALUES ( "1" , "dev001","kingchou", 20);
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)VALUES ( "2" , "dev001", "stef", 22);
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)VALUES ( "1" , "sales001", "long", 26);
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)VALUES ( "2" , "sales001", "yangzk", 27);
INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge)VALUES ( "3" , "sales001", "beansoft", 26);

执行完毕后就能在表T_Person中的看到下面的数据:

FNumber FDepartmentNumber FName FAge
1 dev001 kingchou 20
2 dev001 stef 22
1 sales001 long 26
2 sales001 yangzk 27
3 sales001 beansoft 26

可以看到FNumber和FDepartmentNumber字段的值在表中都有重复的值,但是没有这两个字段同时重复的值,如果这两个字段同时重复的话执行就会失败,执行下面的SQL语句来验证一下:

INSERT INTO T_Person (FNumber, FDepartmentNumber,FName, FAge) VALUES ( "2" , "sales001", "daxia", 30);

因为FNumber等于"2"且FDepartmentNumber等于"sales001"的记录在表中已经存在了,所以在数据库中执行此SQL语句后数据库会报出下面错误信息:

违反了 UNIQUE KEY 约束 "unic_dep_num"。不能在对象 "dbo.T_Person" 中插入重复键。

为了运行后面的例子,请首先将表T_Person删除:DROP TABLE T_Person

可以在一个表中添加多个复合唯一约束,只要为它们指定不同的名称即可。
数据准备:创建表T_Person,并且为字段FNumber和FDepartmentNumber创建一个复合唯一约束以及为FDepartmentNumber和FName创建一个复合唯一约束:

MYSQL、MSSQLServer:
CREATE TABLE T_Person (FNumber VARCHAR(20),
FDepartmentNumber VARCHAR(20),
FName VARCHAR(20),FAge INT,
CONSTRAINT unic_1 UNIQUE(FNumber,FDepartmentNumber),
CONSTRAINT unic_2 UNIQUE(FDepartmentNumber, FName))

Oracle:
CREATE TABLE T_Person (FNumber VARCHAR2(20),FDepartmentNumber VARCHAR(20),FName VARCHAR2(20),FAge NUMBER (10) ,CONSTRAINT unic_1 UNIQUE(FNumber,FDepartmentNumber),CONSTRAINT unic_2 UNIQUE(FDepartmentNumber, FName))

DB2:
CREATE TABLE T_Person (FNumber VARCHAR(20) NOT NULL,FDepartmentNumber VARCHAR(20) NOT NULL,FName VARCHAR(20) NOT NULL,FAge INT NOT NULL,CONSTRAINT unic_1 UNIQUE(FNumber,FDepartmentNumber) ,CONSTRAINT unic_2 UNIQUE(FDepartmentNumber, FName))

在已经创建好的数据表上添加新的唯一约束,使用ALTER TABLE 语句,语法如下:

ALTER TABLE 表名ADD CONSTRAINT 唯一约束名UNIQUE(字段1,字段2……字段n)

如:为T_Person表添加一个建立在字段FName和字段FAge上的新的唯一约束:

ALTER TABLE T_Person ADD CONSTRAINT unic_3 UNIQUE(FName, FAge)

ALTER TABLE语句也可以删除已经创建好的复合唯一约束,语法如下:

ALTER TABLE 表名DROP CONSTRAINT 唯一约束名

不过上边的语法不能在MYSQL中执行,MYSQL中删除约束的语法为:

ALTER TABLE 表名DROP INDEX 唯一约束名

如:将刚才创建的三个复合唯一约束删除:

MSQLServer、Oracle、DB2:
ALTER TABLE T_Person DROP CONSTRAINT unic_1;
ALTER TABLE T_Person DROP CONSTRAINT unic_2;
ALTER TABLE T_Person DROP CONSTRAINT unic_3;

MYSQL:
ALTER TABLE T_Person DROP INDEX unic_1;
ALTER TABLE T_Person DROP INDEX unic_2;
ALTER TABLE T_Person DROP INDEX unic_3;

相关文章

  • 五、SQL–索引/约束③(唯一约束)

    唯一约束又称为UNIQUE约束,它用于防止一个特定的列中两个记录具有一致的值如:在员工信息表中希望防止两个或者多个...

  • 五、SQL–索引/约束⑤(主键约束)

    第一范式要求每张表都要有主键,因此主键约束是非常重要的,而且*主键约束是外键关联的基础条件。主键约束为表之间的关联...

  • 五、SQL–索引/约束④(check约束)

    CHECK约束会: 检查输入到记录中的值是否满足一个条件,如果不满足这个条件则对数据库做的修改不会成功。如,一个人...

  • 五、SQL—索引/约束①

    在数据库系统中解决问题的技术就是索引与约束。索引用来提高数据的检索速度约束则用来保证数据的完整性 索引 索引是建立...

  • 五、SQL–索引/约束②(非空约束)

    在定义数据表的时候,默认情况下所有字段都是允许为空值的,如果需要禁止字段为空,那么就需要在创建表的时候显示指定。指...

  • 五、SQL–索引/约束⑥(外键约束)

    引出:当一些信息在表中重复出现时,就要考虑要将它们提取到新的表中,并在源表中引用新创建的中的数据。 如:很多作者都...

  • 面试积累之数据库篇(六)

    事务的4大属性:原子性,隔离性,一致性,持久性 sql完整性约束:主键约束唯一约束检查约束外键约束默认约束 sql...

  • Oracle索引

    索引具有两个功能:强制实施主键约束和唯一约束、提高性能。 索引的必要性: 主键和具有唯一性约束的列都会自动...

  • (八)列属性(列约束)

    1. PRIMARY 主键 2. UNIQUE 唯一索引(唯一约束) 3. NULL 约束 4. DEFAULT ...

  • UNIQUE ,PRIMARY KEY

    GO ON----(SQL高级) SQL UNIQUE 约束 UNIQUE 约束唯一标识数据库表中的每条记录。 U...

网友评论

      本文标题:五、SQL–索引/约束③(唯一约束)

      本文链接:https://www.haomeiwen.com/subject/knhnihtx.html