笔记疑问:ACCESS中一直没能找到用sql语句更改列名、创建约束、设置默认值。
经过测试,建表时也不能同时设置默认值。如下列建表语句会报错(但把第三句的default 0去掉就能执行):
create table test (
id counter,
name char(20) not null,
age integer not null default 0,
notes memo,
primary key (id)
);
1、建表语句:
autincrement就跟MsSqlServer中的identity (1,1)一样的效果,实现自增量。
create table test (
Sn autoincrement,
testId integer not null,
testName char(30) not null,
testMemo memo null,
sdate datetime,
testCol text null,
primary key (Sn)
);
//自动增量,也可在建表时表述为:Id counter,
独立增加主键:
alter table test add constraint pk_testid primary key (id);
增加外接关联表:
注意:下面这句在access执行不了:
alter table newsinfo add constraint fk_clid FOREIGN KEY (newsclassid) references newsclass (id) ON UPDATE CASCADE ON DELETE CASCADE;
正确能执行的:
alter table newsinfo add constraint fk_clid FOREIGN KEY (newsclassid) references newsclass (id);
ALTER TABLE Table2 ADD CONSTRAINT Relation1 FOREIGN KEY ([Id]) REFERENCES Table1 ([Id]) ;
删除外接关联关系:
alter table newsinfo drop constraint fk_clid;
2、修改列类型:
改变列testcol的类型为memo,如:
alter table test alter column testcol memo;
alter table test alter column testcol long;
alter table test alter column testcol float;
alter table test alter column testcol money;
alter table test alter column testcol currency;
增加列:
alter table test add testcoladd long;
删除列:
alter table test drop testcoladd;