T-SQL创建数据表

注意：在MISDB数据库创建Department（部门表）、Post（职位表）、Employee（员工表）三个表，其中员工表中的部门ID、职位ID使用外键引用部门表与职位表字段。

use MISDB
go

if exists (select * from sysobjects where name='Department')
drop table Department
go
--创建部门表
create table Department
(
	DepartmentId int identity(10,1) primary key,--主键标识列；identity(10,1)代表自增长从10开始依次增加1.
	DepartmenName varchar(50) not null
)
go

if exists(select * from sysobjects where name='Poat')
drop table Post
go
--创建职位表
create table Post
(
	PostId int identity(10,1) primary key,
	PostName varchar(50) not null
)
go

if exists(select * from sysobjects where name='Employee')
drop table Employee
go
--创建员工表
create table Employee
(
	EmployeeId int identity(1000,1) primary key,
	EmployeeName varchar(50) not null,
	Gender char(2) not null,
	NowAddress nvarchar(100) default('地址不详'),--默认值
	IdNo char(18) not null check(len(IdNo)=18),--检查约束，IdNo字段的长度为18才能写入
	WeiXinNumber varchar(20) not null,
	PhoneNumber varchar(50) not null,
	OtherWork nvarchar(50) not null,
	EntryDate Datetime not null,
	PostId int references Post(PostId),--职位编号（外键）引用职位表
	DepartmentId int references Department(DepartmentId)--部门编号（外键）引用部门表
)
go