SQL SERVER2005——ALTER TABLE

use excel
go
CREATE TABLE doc_exa( col1 int)
go
alter table doc_exa add clo2 char(2)  --添加字段
go
alter table doc_exa alter column col2 numeric   --更改数据类型由int更改为numeric
go
exec sp_help doc_exa
go
alter table doc_exa drop column clo2  --删除字段
go
alter table doc_exa add clo3 varchar(10) null     --添加unique  约束
constraint clo3_unique unique
go 
alter table doc_exa add constraint pk_doc primary key CLUSTERED (col1)  -- 添加主键约束
go
alter table doc_exa add col_check int    --添加check 约束的列
constraint col_check check (col_check>=1)
go
alter table doc_exa drop constraint col_check    --修改check约束需要先drop后add
alter table doc_exa with nocheck add  constraint col_check check (col_check>=8)

 

create table doc_exe (column_a int constraint column_a_un unique)
go
alter table doc_exe add
column_b numeric constraint column_b_def default 50;
go
alter table doc_exe add column_c varchar(5)
insert into doc_exe (column_a)values(4)
alter table doc_exe add constraint column_c_def default 'yyyy' for column_c  --给现有的字段添加default约束
go
alter table doc_exe
add addDate smalldatetime null
constraint add_deflt default getdate() with values;  --获取默认当前的时间
GO
alter table _doc_exe add

CREATE TABLE doc_exf ( column_a INT) ;
GO
INSERT INTO doc_exf
VALUES (1) ;
GO
ALTER TABLE doc_exf
ADD AddDate smalldatetime NULL
CONSTRAINT AddDateDflt
DEFAULT GETDATE() WITH VALUES
GO

 

--禁用和重启约束

CREATE TABLE cnst_example
(id INT NOT NULL,
    name VARCHAR(10) NOT NULL,
    salary MONEY NOT NULL
    CONSTRAINT salary_cap CHECK (salary < 100000)
)

-- Valid inserts
INSERT INTO cnst_example VALUES (1,'Joe Brown',65000)
INSERT INTO cnst_example VALUES (2,'Mary Smith',75000)

-- This insert violates the constraint.
INSERT INTO cnst_example VALUES (3,'Pat Jones',105000)

-- Disable the constraint and try again.
ALTER TABLE cnst_example NOCHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (3,'Pat Jones',105000)

-- Re-enable the constraint and try another insert; this will fail.
ALTER TABLE cnst_example CHECK CONSTRAINT salary_cap
INSERT INTO cnst_example VALUES (4,'Eric James',110000) ;

--禁用和重新启用触发器

create table table_trig(id int ,name varchar(10),salary money);
go
create trigger trig1 on table_trig for insert   --创建触发器
as
if(select count(*) from table_trig where salary>100000)>0
begin
 print 'TRIG1 Error: you attempted to insert a salary > $100,000'
    ROLLBACK TRANSACTION
end;
go
INSERT INTO table_trig VALUES (1,'Pat Smith',100001) ;
go
ALTER TABLE table_trig disable trigger trig1;  --禁用触发器
ALTER TABLE table_trig alter column id int not null
go
INSERT INTO table_trig VALUES (1,'Pat Smith',100001) ;
go
ALTER TABLE  table_trig enable trigger trig1  ;  --启用触发器
go
INSERT INTO table_trig VALUES (1,'Pat Smith',100003) ;
go
alter table table_trig with nocheck   --添加 PRIMARY KEY 约束
add constraint pk_table_trig_id primary key clustered (id)
WITH (FILLFACTOR = 75, ONLINE = ON, PAD_INDEX = ON)
go

--添加和删除 FOREIGN KEY 约束
create table　contactBak(contactid int);
go
alter table contactBak add constraint fk_contactBak_contact foreign key (contactid)  --添加外键约束
references contact(contractid);
go
alter table contactBak drop constraint fk_contactBak_contact   --删除外键约束