create table products
(
p_id char(8) not null,
p_name char(10) not null,
price money default 0.01,
quantity smallint null ,
constraint pk_p_id primary key(p_id,p_name)
)
select * from products
insert into products(p_id,p_name,price,quantity) values ('1','aaa',1000,9)
insert into products(p_id,p_name,price,quantity) values ('3','aaa',1000,9)
insert into products(p_id,p_name,price,quantity) values ('2','aaa1',1000,9)
create table orders
(
order_id char(8),
p_id char(8),
p_name char(10),
constraint pk_order_id primary key(order_id),
foreign key(p_id,p_name) references products(p_id,p_name)
)
insert into orders(order_id,p_id,p_name) values ('o5','4','aaa')
select * from orders
create table employees
(
emp_id char(8),
emp_name char(10),
emp_cardid char(18),
constraint pk_emp_id primary key(emp_id),
constraint uk_emp_cardid unique(emp_cardid)
)
insert into employees(emp_id,emp_name,emp_cardid) values ('2','zhangsang','367887878787889')
select * from orders2
create table orders2
(
order_id char(8),
p_id char(8),
p_name char(10),
quantity smallint,
constraint pk_order_id1 primary key(order_id),
constraint chk_quantity check (quantity>=10)
)
select * from orders2
insert into orders2(order_id,p_id,p_name,quantity) values ('4','oo1','aaa',10)
alter table orders2
add bydate datetime
alter table orders2
add constraint def_bydate default getdate() for bydate
select * from orders2
alter table orders2 nocheck constraint chk_quantity
alter table orders2 check constraint chk_quantity
alter table orders2 nocheck constraint def_bydate
create rule age_rule as @value between 18 and 35
sp_helptext age_rule
select * from student
alter table student
add age int
insert into student(stuName,age) values ('ddd',17)
sp_bindrule age_rule ,'student.[age]'
sp_unbindrule 'student.[age]'
create default xb_sex as 'll'
sp_bindefault xb_sex,'student.[sex]'
sp_unbindefault 'student.[sex]'