--如果testdb不存在,创建testdb数据库
if DB_ID('testdb') is null
create database testdb;
use testdb
--如果表dbo.orders已经存在了,则删除该表
if OBJECT_ID('dbo.orders','U') is not null
drop table dbo.orders;
--如果表dbo.employees已经存在了,则删除该表
if OBJECT_ID('dbo.Employees', 'U') is not null
drop table dbo.employees;
--创建表dbo.employees
create table dbo.employees
(
employeeid int not null,
firstname varchar(30) not null,
lastname varchar(30) not null,
mgrid int null,
salary int not null,
--设置主键
constraint PK_Employees primary key(employeeid),
--设置外键
constraint FK_Employees foreign key(mgrid) references dbo.employees(employeeid),
--设置检查约束
constraint CK_Employees check(salary>0)
);
--添加唯一约束
alter table dbo.employees
add constraint UK_Employees
unique (mgrid);
create table dbo.orders
(
orderid int not null,
employeeid int not null,
orderts datetime not null,
constraint PK_Orders primary key (orderid)
);
--外键约束 级联删除 cascade, set default, set null
alter table dbo.orders
add constraint FK_Orders
foreign key(employeeid) references dbo.employees(employeeid) on delete cascade
--默认值约束
alter table dbo.orders
add constraint DK_Orders
default(current_timestamp) for orderts
第一章 T-SQL 查询和编程基础
最新推荐文章于 2012-09-06 18:00:43 发布