alter table添加表约束

时间:2024-03-12 09:57:36

翻阅了一下网上关于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)