第一章 T-SQL 查询和编程基础

--如果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