日期:2014-05-18  浏览次数:20403 次

有关default 设置
go
create database stu
go
create table person
(
id int not null primary key identity,
stuname char(8) not null ,
sex char not null default sex in('1','0'),
age int not null,
stuno varchar(20) not null
)
create tabble person1
(
pid int not null primary key identity,
pno varchar(20) not null foreign key (pno) references person(stuno),
psex char not null default psex in('男','女'),
age int not null
)








消息 128,级别 15,状态 1,第 5 行
在此上下文中不允许使用名称 "sex"。有效表达式包括常量、常量表达式和变量(在某些上下文中)。不允许使用列名。

------解决方案--------------------
SQL code
create table person
(
id int not null primary key identity,
stuname char(8) not null ,
sex char not null default('') ,
age int not null,
stuno varchar(20) not null
)
ALTER TABLE [person]  WITH CHECK ADD  CONSTRAINT [CK_person] CHECK  (([sex]='1' OR [sex]='0'))
或
ALTER TABLE [person1]  WITH CHECK ADD  CONSTRAINT [CK_person1] CHECK  (([sex]='男' OR [sex]='女'))
GO
 

GO
---改成添加约束

------解决方案--------------------
SQL code

create database stu
go

create table person
(
id int not null primary key identity,
stuname char(8) not null ,
sex char not null constraint ck_sex check(sex in('1','0')),
age int not null,
stuno varchar(20) not null
)

create table person1
(
pid int not null identity primary key ,
pno varchar(20) not null,
psex char not null constraint ck_psex check(psex in('男','女')),
age int not null
)

------解决方案--------------------
第二个表应用的外键也有问题
SQL code

create table person
(
id int not null  identity,
stuname char(8) not null ,
sex char not null default('1')  CONSTRAINT [CK_person] CHECK  (([sex]='1' OR [sex]='0')) ,
age int not null,
stuno varchar(20) not null primary key
)

create table person1
(
pid int not null primary key identity,
pno varchar(20) not null foreign key (pno) references person(stuno),
psex Nchar(2) not null default ('男') CONSTRAINT [CK_person1] CHECK  (([psex]='男' OR [psex]='女')),
age int not null
)

GO