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