alter table添加表约束

翻阅了一下网上关于alter table添加表约束的资料，学习下，然后供自己以后使用。

仅仅供自己使用。。。

 

 

总结alter table ### add constraint ## 使用方法

添加表约束

 

首先看下alter table的定义（百度百科）：

Alter table，网络程序及编程中所用的术语。通过更改、添加、除去列和约束，或者通过启用或禁用约束和触发器来更改表的定义。

 

数据库SQL语言的修改语句，可以用来修改基本表，其一般表示格式为：

ALTER TABLE<表名>[改变方式]

改变方式：

· 加一个栏位: ADD "栏位 1" "栏位 1 资料种类"

· 删去一个栏位: DROP "栏位 1"

· 改变栏位名称: CHANGE "原本栏位名" "新栏位名" "新栏位名资料种类"

· 改变栏位的资料种类: MODIFY "栏位 1" "新资料种类"

由上可以看出，修改基本表提供如下四种修改方式：

（1）ADD方式：用于增加新列和完整性约束，列的定义方式同CREARE TABLE语句中的列定义方式相同，其语法格式：

ALTER TABLE <表名> ADD <列定义>|<完整性约束>。由于使用此方式中增加的新列自动填充NULL值，所以不能为增加的新列指定NOT NULL约束。

（2）DROP方式：用于删除指定的完整性约束条件，或删指定的列，其语法格式为：

ALTER TABLE<表名> DROP [<完整性约束名>]

ALTER TABLE<表名> DROP COLUMN <列名>

注释：某些数据库系统不允许这种在数据库表中删除列的方式 (DROP COLUMN <列名>)。

(3)CHANGE方式，用于修改某些列，其语法格式：

ALTER TABLE [表名] CHANGE <原列名> TO <新列名><新列的数据类型>

(4)MODIFY方式，用于修改某些列的数据类型，其语法格式：

ALTER TABLE [表名] MODIFY [列名] [数据类型]

 

先创建的库个表 如下 : 下面示例用到表结构

create database Text_DB

go

use Text_DB

go

create table Teacher

(

TeaId int not null ,

TeaName varchar(20) not null,

TeaAge  int ,

TeaAddress  varchar(50)

)--为了更直观的看到效果 这里就先不添加约束

 

 

一.Alter 主要作用针对是给已经创建的表进行添加和修改删除约束 的操作

1.给已经创建的表添加约束

   分为两种方式,一种是在创建约束 并同时给所创约束定义一个名字  这样可以再次进行操作,比如删除时候要用到

   另一种方式是直接创建约束 不命名的,但是再次操作时候是不方便的

第一种:  

Alter table 表名 add constraint 约束 字 约束类型(列名)

例:

-----添加约束(命名)-----------

alter table Teacher add constraint PK_1 primary key (TeaId)--主键约束

alter table Teacher add constraint UN_1 unique(TeaName)--唯一约束

alter table Teacher add constraint CK_1 check (TeaAge>0)--范围约束

alter table Teacher add constraint DE_1 default '123' for TeaAddress --默认约束

--外键约束,这里就不一一举例了,语法如下

alter table 表名 add constraint 约束名  foreign key(关联字段) references 主表(关联字段)

 

第二种:

  Alter table 表名 add 约束(列名)

例: 分别给 Teacher表中添加 主键 唯一 和范围约束

  alter table Teacher add primary key (TeaId)--主键

alter table Teacher add unique(TeaName)--唯一

alter table Teacher add check (TeaAge>0)--范围约束

alter table Teacher add default '123' for TeaAddress --默认约束

alter table 表名 add foreign key(列名) references 主表(列名)--外键

 

2.删除约束: 语法

 alter table 表名 drop constraint 约束名--删除约束

例:(删除Teacher表中的约束)

  alter table Teacher drop constraint PK_1 --删除主键约束

alter table Teacher drop constraint UN_1 --删除唯一约束

alter table Teacher drop constraint CK_1 --删除范围约束

alter table Teacher drop constraint DE_1 --删除默认约束

 

 

 

二. Alter table 用法  针对表结构进行修改,比如添加列删除列等等...

   1.添加列 语法

 Alter table 表名 ADD 列名  数据类型

例: (给Teacher 表中添加一列 notes 数据类型为varchar(200))

alter table Teacher add notes varchar(200)

2.删除表中的列 语法

  Alter table 表名 drop column 列名

例:(删除表中的notes列)

alter table Teacher drop column notes

3.修改某一列中的数据类型语法

Alter table 表名 alter column 列名 数据类型

例:(将Teacher 表中的notes列更改为int类型)

alter table Teacher alter column notes int

 

ALTER TABLE ADD增加多个字段外键约束

/* 创建表 及设置 主键 */

 CREATE TABLE TABLE_USER

(USER_ID INT IDENTITY(1,1) NOT NULL,

 USER_NAME NVARCHAR(40) NOT NULL,

 LAST_UPDATED_BY    NVARCHAR(15),

 LAST_UPDATED_DATE    DATETIME,

CONSTRAINT USER_PK PRIMARY KEY (USER_ID)

);

/*ALTER TABLE 增加多个字段 */

ALTER TABLE TABLE_USER ADD 

 DEPARTMENT_ID INT NOT NULL,COMPANY_ID INT NOT NULL,TEMP_COL NVARCHAR(10);

/*ALTER TABLE 增加多个外键 */

ALTER TABLE TABLE_USER ADD 

CONSTRAINT USER_DEPARTMENT_FK FOREIGN KEY(DEPARTMENT_ID) REFERENCES TABLE_DEPARTMENT(DEPARTMENT_ID) ,CONSTRAINT USER_COMPANY_FK FOREIGN KEY(COMPANY_ID) REFERENCES TABLE_COMPANY(COMPANY_ID)  ;

/*ALTER TABLE 删除字段 */

ALTER TABLE TABLE_USER DROP COLUMN TEMP_COL;

 

--删除约束 --先查出约束名 EXEC sys.sp_helpconstraint @objname = N'bulkinserttest' --表名 ALTER TABLE dbo.bulkinserttest DROP CONSTRAINT ck_bulkinsert

例子：

向表中增加一个 varchar 列：

ALTER TABLE distributors ADD address varchar(30);

从表中删除一个字段：

ALTER TABLE distributors DROP COLUMN address RESTRICT;

在一个操作中修改两个现有字段的类型：

ALTER TABLE distributors

ALTER COLUMN address TYPE varchar(80),

ALTER COLUMN name TYPE varchar(100);

使用一个 USING 子句， 把一个包含 UNIX 时间戳的 integer 字段转化成 timestamp with time zone：

ALTER TABLE foo

ALTER COLUMN foo_timestamp TYPE timestamp with time zone

USING

timestamp with time zone 'epoch' + foo_timestamp * interval '1 second';

对现存字段改名：

ALTER TABLE distributors RENAME COLUMN address TO city;

更改现存表的名字：

ALTER TABLE distributors RENAME TO suppliers;

给一个字段增加一个非空约束：

ALTER TABLE distributors ALTER COLUMN street SET NOT NULL;

从一个字段里删除一个非空约束：

ALTER TABLE distributors ALTER COLUMN street DROP NOT NULL;

给一个表增加一个检查约束：

ALTER TABLE distributors ADD CONSTRAINT zipchk CHECK (char_length(zipcode) = 5);

删除一个表和它的所有子表的检查约束：

ALTER TABLE distributors DROP CONSTRAINT zipchk;

向表中增加一个外键约束：

ALTER TABLE distributors ADD CONSTRAINT distfk FOREIGN KEY (address) REFERENCES addresses(address) MATCH FULL;

给表增加一个（多字段）唯一约束：

ALTER TABLE distributors ADD CONSTRAINT dist_id_zipcode_key UNIQUE (dist_id, zipcode);

给一个表增加一个自动命名的主键约束，要注意的是一个表只能有一个主键：

ALTER TABLE distributors ADD PRIMARY KEY (dist_id);

把表移动到另外一个表空间：

ALTER TABLE distributors SET TABLESPACE fasttablespace;

 

 

1. 主键约束

要对一个列加主键约束的话，这列就必须要满足的条件就是非空。因为主键约束：就是对一个列进行了约束，约束为（非空、不重复）。

【格式】alter table 表格名称 add constraint 约束名称 增加的约束类型 （列名）

例子：要对一个列加主键，列名为id,表名为emp

alter table emp add constraint ppp primary key (id)

 

2. check约束

就是给一列的数据进行了限制

【格式】alter table 表名称 add constraint 约束名称 增加的约束类型 （列名）

例子：年龄列的数据都要大于20，表名（emp） 列名（age）

alter table emp add constraint xxx check(age>20)

某一列(sex)取值只能为“男”和“女”的约束表达式：

ALTER TABLE 表的名称 ADD CONSTRAINT 约束的名称 CHECK (sex in ('男','女'))

 

3. unique约束

这样的约束就是给列的数据追加的不重复的约束类型

【格式】alter table 表名 add constraint 约束名称 约束类型（列名）

例子：给ename列加个unique，让ename列的数据不重复

alter table emp add constraint qwe unique(ename)

 

4. 默认约束

意思很简单，就是让此列的数据默认为一定的数据

【格式】alter table 表名称 add constraint 约束名称 约束类型 默认值 for 列名

例子：emp表中的gongzi列默认为10000

alter table emp add constraint jfsd default 10000 for gongzi

 

5. 外键约束

这个有点难理解了，外键其实就是引用。

因为主键实现了实体的完整性，外键实现了引用的完整性，应用完整性规定，所引用的数据必须存在！其实就是个引用。

【格式】alter table 表名 add constraint 约束名称 约束类型 (列名) references 被引用的表名称 （列名）

例子：

一个表名称叫dept 里面有2列数据 一列是ID一列是ENAME

id：表示产品的编号

ename：表示产品的名称

另外一个表格名称是emp 里面有2列数据，一列是ID 一列是DID

id:表示用户号

did：表示购买的产品号

要让emp表中的did列去引用dept表中的id

可以用下面的方法

alter table emp add constraint jfkdsj foreign key (did) references dept (id)