使用脚本创建SQL Server数据库数据表及相关约束

--创建数据库
use master
go
if exists(select * from sysdatabases where name = 'StudentManageDB')
drop database StudentManageDB
create database StudentManageDB
on primary
(
  name = 'StudentManageDB',
  filename = 'D:\DB\StudentManageDB.mdf',
  size=5MB,
  filegrowth=5MB
),
(
  name = 'StudentManageDB1',
  filename = 'D:\DB\StudentManageDB.ndf',
  size=5MB,
  filegrowth=5MB
)
log on
(
  name = 'StudentManageDBLog',
  filename = 'D:\DB\StudentManageDB.ldf',
  size=5MB,
  filegrowth=5MB
)
--创建数据学生表
use StudentManageDB
go
if exists(select * from sysobjects where name = 'Students')
drop table Students
create table Students
(
  StudentId int identity(10000,1),
  StudentName nvarchar(20) not null,
  Gender nvarchar(2) not null,
  Age int not null,
  Birthday datetime not null,
  StudentIdNo numeric(18,0) not null,
  ClassId int not null
)
go


--创建班级表
go
if exists (select * from sysobjects where name = 'StudentClass')
drop table StudentClass
go
create table StudentClass
(
  ClassId int primary key,
  ClassName nvarchar(20) not null
)

--创建成绩表
go
if exists (select * from sysobjects where name = 'Score')
drop table Score
go
create table Score
(
  StudentId int primary key,
  CSharp int null,
  SQLServerDB int null,
  UpdateTime datetime not null
)
go

--创建管理员表
use StudentManageDB
go
if exists (select * from sysobjects where name = 'Admins')
drop table Admins
go
create table Admins
(
  LoginId int  primary key,
  Loginpwd int not null,
  AdminName nvarchar(20) not null
)
go

--创建主键约束
if exists(select * from sysobjects where name = 'PK_StudentId')
alter table Students drop constraint PK_StudentId
alter table Students add constraint PK_StudentId primary key(StudentId)
go

--创建唯一约束
if exists(select * from sysobjects where name = 'UK_StudentIdNo')
alter table Students drop constraint UK_StudentIdNo
alter table Students add constraint UK_StudentIdNo unique(StudentIdNo)

--创建Check约束
if exists(select * from sysobjects where name = 'CK_Age')
alter table Students drop constraint CK_Age
alter table Students add constraint CK_Age check(Age between 18 and 25)

--创建外键约束
if exists(select * from sysobjects where name = 'FK_ClassId')
alter table Students drop constraint FK_ClassId
alter table Students add constraint FK_ClassId foreign key(ClassId) references StudentClass(ClassId)