SQL基础(1)：约束

最新推荐文章于 2022-04-25 11:13:21 发布

milanleon

最新推荐文章于 2022-04-25 11:13:21 发布

阅读量1k

点赞数

分类专栏：数据库文章标签：数据库

本文链接：https://blog.csdn.net/milanleon/article/details/40394665

版权

数据库专栏收录该内容

4 篇文章 0 订阅

订阅专栏

以下只针对SQL Server，其它数据库类型可能不支持某些操作或操作名不同。

SQL语句分类：

①、DCL：用户授权，grant、revoke。
②、DDL：操作表，create、alter、drop、truncate。
③、DML：操作数据：insert、update、delete。
④、查询：select。
⑤、事务控制：commit、rollback、savepoint。

create table 用来创建表。

not null：非空约束

该列值不能为空。

create table person([ID] int not null, --ID列中值不能为空值
		  [name] varchar(20),
	           [city] varchar(30));

unique：值唯一约束

该列不能有相同的数值，sql server中空值会作为相同的值来处理，MySQL则不会。

值唯一的列会自动建立索引。

constraint 用来定义有名约束。

create table person([ID] int not null unique,--无名值唯一约束
		  [name] varchar(20) ,
		  [city] varchar(30), 
		  );
		  
create table person([ID] int not null constraint uq1_person unique,--有名值唯一约束
		  [name] varchar(20) constraint uq2_person unique,--有名值唯一约束
		  [city] varchar(30), 
		  );
		  
create table person([ID] int not null unique,--无名值唯一约束
		  [name] varchar(20) ,
		  [city] varchar(30)
		  constraint person_uk unique(name, city) --有名多列值唯一约束
		  );
		  
alter table person add unique([name]), unique([city]); //在表已经存在的情况建立无名值唯一约束
					
alter table person add constraint uq1_person unique([ID]), constraint uq2_person unique([name]);--在表已经存在的情况下创建有名值唯一约束
 
alter table person drop constraint uq1_person, uq2_person;--撤消值唯一约束

alter table person modify name varchar[255] unique; --对MySQL，更改列的数据类型时增加值唯一约束
alter table person drop index uq1_person, uq2_person; --对于MySQL, 撤消值唯一约束

identity：自增长标识字段

当插入新的数据时，该列的字段会自动填充一个数字，数值默认起始值是1，逐渐递增。

create table test_table1([ID] int not null identity,//起始值为1，逐渐递增1
		       [name] varchar(20),
		       [city] varchar(30));
						
create table test_table2([ID] int not null identity(10, 2),//起始值为10,逐渐递增2
		       [name] varchar(20),
		       [city] varchar(30));

使用insert插入一条记录的时候，对于identiry标识字段可以不插入数据，使用其默认值，如：

create table person(
             [ID] int primary key identity,
			 [name] varchar(20),
			 [city] varchar(20)
			);
			
insert person values('张三', '北京');
insert person([name], [city]) values('李四', '上海');
insert person values('王五', '广州');
insert person([name], [city]) values('赵六', '深圳');

select * from person;

输出为：

ID name city
1 张三北京
2 李四上海
3 王五广州

4 赵六深圳

对于MySQL，使用auto_increment来设置自增长标识字段。

primary key：主键约束

我们把表中的列称为“字段”，也叫做“属性”，一行数据叫做一条“记录”，又称“元组”。存放在表中行列交叉处的数据叫做“值”，它的位置由这个表中的记录和字段来定义。

主键是表里用于表示记录唯一性的字段，故主键的键值不能重复，且不能为空值；主键可以由一个或多个字段组成，表中每一行都由它的主键来唯一标识；每个表都应有一个主键，且表只能有一个主键。

primary key自动拥有unique约束和not null约束，故不能有重复的值和空值。一般为了方便还人工将主键添加identity属性。主键会自动建立索引。

create table person(
		    [ID] int identity primary key,--无名主键约束
		    [name] varchar(20),
		    [city] varchar(30)
		    );
					
create table person(
		[ID] int identity constraint pk_person primary key,--有名主键约束
		[name] varchar(20) not null,  
		[city] varchar(30)
          );
					
alter table person add constraint pk_person primary key([ID]);--在表已经存在的情况下创建有名主键

alter table person drop constraint pk_person;--撤销主键约

对于MySQL，主键名只能为默认的PRIMARY, 删除主键为：

alter table person drop primary key;

主键可以由多个字段组成，即组合主键：

create table persons(
				[ID] int,
				[name] varchar(10),
				[city] varchar(10),
				constraint pk_persons primary key([ID], [name])//有名组合主键				
			 );

一般用数字表示主键，不建议用字符串来表示主键。

主键的值通常都不允许修改，如果会改动主键的值，那么就是主键定义有误。

一般将主键定义成"表名_ID"，如persons_ID。

references/foreign key references：外键约束

外键的值来自另一个表里的值唯一字段（经常为另一个表的主键），通常通过设置外键来体现主表和从表之间的一对多关系（如老师—学生），或者一对一关系（如国家—首都，此时的外键列应该为值唯一）。

外键可以有重复的值，也可以为空值，如以下表order_的外键customer_ID来自表customer的主键ID：

需要注意的是外键的值也可以来自本表的主键，这称为“自关联”。

customer

ID name age city
1 张三 25 北京
2 李四 30 上海
3 王五 28 上海
4 赵六 32 广州
5 孙七 30 重庆

order_

order_number time customer_ID
10001 2014-10-10 13:05:56.233 3
10002 2014-10-10 13:20:05.120 3
10003 2014-10-10 13:40:13.653 3
10004 2014-10-10 14:20:05.120 1

一个表中可以有两个或多个外键，所以这个表可以体现与另外两个表之间的关系。

还可以将这两个外键字段设置为这张表的组合主键，如果两个外键组合没有重复值的话。

create table books(
				    [ID] int not null,
				    [name] varchar(20),
				    [city] varchar(30),
				    [ID_p1] char(3) not null references titles([title_id])--无名外键约束
				   );			
create table books(
				    [ID] int not null,
				    [name] varchar(20),
				    [city] varchar(30),
				    [ID_p1] char(3) not null,
				    foreign key([ID_p1]) references titles([title_id])--无名外键约束
				   );	
				   
create table books(
	       [ID] int not null,  
                [name] varchar(20),  
                [city] varchar(30),  
                [ID_p1] int not null constraint fk1_books foreign key references person1([ID]),--有名外键约束 
                [ID_p2] int not null constraint fk2_books foreign key references person2([ID])--有名外键约束
              );  
              
create table books(
    [ID] int not null,
    [name] varchar(20),
    [city] varchar(30),
    [ID_p1] char(3) not null,
    constraint fk_books foreign key([ID_p1]) references titles([title_id])--有名外键约束
   );	
 
alter table books add constraint fk1_books foreign key([ID_p1]) references person1([ID]),
		    constraint fk2_books foreign key([ID_p2]) references person2([ID]);
					  --在表已存在的情况下创建两个有名外键
						   
alter table books drop constraint fk1_books, fk2_books;--撤销外键约束

数据库中有一个数据库关系图选项，右击选择新建数据库关系图，选择要查看的有关的表，就可以得到一张图，这个图里面显示了各个表的外键来自哪个表中的主键，右击表还可以手动向这个表添加和删除约束、索引等。

含有外键的表叫外键（从）表，外键字段来自的那张表叫主键（主）表。

在删除表时如果出错，可能是因为其它表的外键引用了本表中的主键。

对于MySQL，外键约束的列会自动建立索引，而且应该通过foreign key references在所有字段定义之后再指定外键，因为使用references直接在字段定义后来建立外键是无效的，删除外键也不太相同。还可以在建立外键语句之后添加on delete cascade（删除主表记录时，从表记录联动删除）或on delete set null（删除主表记录时，从表记录设为null）。

alter table books drop foreign key fk1_books, fk2_books;--对于MySQL, 撤销外键约束

check()：值约束或值范围约束

create table persom(
					 [ID] int not null constraint ck_persom primary key check ([ID]>0 and [ID]<1000),--无名值范围约束
					 [name] varchar(20),
					 [city] varchar(30)
					);
					
create table persom(
                     [ID] int not null constraint pk_persom primary key constraint ck1_persom check([ID]>0 and [ID]<1000),--有名值范围约束  
                     [name] varchar(20),  
                     [city] varchar(30) constraint ck2_persom check([city]='beijing')--有名值范围约束
                      );

alter table persom add constraint ck1_persom check([ID]>0 and [city]='beijing'),
                       constraint ck2_persom check([city]='北京');--在表已经存在的情况下创建值范围约束

alter table persom drop constraint ck1_persom, ck2_persom;--撤销值范围约束

目前MySQL的check约束无效，可以借助触发器来实现。

default：默认值约束

create table person(
					 [ID] int not null constraint pk_person primary key,
					 [name] varchar(20),
					 [time] datetime default getdate()--无名默认值约束
					);
					
create table person(
					 [ID] int not null constraint pk_person primary key,
					 [name] varchar(20),
					 [city] varchar(30) constraint df1_person default 'beijing',--有名默认值约束 
					 [time] datetime constraint df2_person default getdate()--有名默认值约束
					);
					
alter table person add constraint df1_person default 'beijing' for [city],
					   constraint df2_person default getdate() for [time];--在表已经存在的情况下创建多个有名默认值约束
					   
alter table person drop constraint df1_person, df2_person;--撤销默认值约束

带默认值的字段插入数据示例：

create table person(
					 [ID] int not null constraint pk_person primary key,
					 [name] varchar(20),
					 [city] varchar(30) constraint df1_person default 'beijing',--有名默认值约束 
					 [time] datetime constraint df2_person default getdate()--有名默认值约束
					);
					
insert into person([ID], [name]) values(1001, 'leon'); 
insert into person([ID], [name], [city]) values(1002, 'leon', 'beijing'); 
insert into person values(1003, 'leon', 'hangzhou', '2000-01-01 01:01:01');

对于MySQL，约束保存在information_schema数据库下的TABLE_CONSTRAINTS表下，可以查询该表以查看各表的约束。