T-SQL create database

 

/* Create a bbsdb database */
Use master
If exists (select * From sysdatabases where name = 'bbsdb ')
Drop database bbsdb
Create Database bbsdb
On
(
Name = 'bbsdb _ data ',
Filename = 'd: \ project \ bbsdb_data.mdf ',
Size = 10,
Filegrowth = 20%
)
Log On
(
Name = 'bbsdb _ log ',
Filename = 'd: \ project \ bbsdb_log.ldf ',
Size = 3,
Maxsize = 20,
Filegrowth = 10%
)

/* Create a bbsusers table */
Use bbsdb
If exists (select * From sysobjects where name = 'bbsusers ')
Drop table bbsusers
Create Table bbsusers
(
UID int identity (1, 1) not null, -- Student ID, ID column
Uname varchar (15) not null, -- user nickname
Upassword varchar (10) not null, -- User Password
Uemail varchar (20), -- email address
Usex bit not null, -- User gender
Uclass int, -- level
Uremark varchar (20), -- Remarks
UregDate datetime not null, -- Registration Date
Ustate int null, -- state
Upoint int null -- user points
)

/* Create constraints in the bbsUsers table */
Alter table bbsUsers
Add constraint PK_uid primary key (uid), -- primary key
Constraint DF_Upassword default (888888) for Upassword, -- the initial password is 888888
Constraint DF_Usex default (1) for Usex, -- default gender is male
Constraint DF_UregDate default (getdate () for UregDate, -- the default registration date is the system date.
Constraint DF_Ustate default (0) for Ustate, -- the status is offline by default.
Constraint DF_Upoint default (20) for Upoint, -- the default point is 20
Constraint CK_Uemail check (Uemail like '% @ %'), -- the email must contain the @ symbol
Constraint ck_upassword check (LEN (upassword)> = 6) -- The password must contain at least six characters.

/* Create a bbssection table */
Use bbsdb
If exists (select * From sysobjects where name = 'bbssection ')
Drop table bbssection
Create Table bbssection
(
Sid int identity (1, 1) not null, -- plate number, automatic Growth
Sname varchar (32) not null, -- Forum name
Smasterid int not null, -- Moderator user ID
Sprofile varchar (20) null, -- layout Overview
Sclickcount int null, -- click rate
Stopiccount int null -- number of senders
)

/* Create constraints in the bbssection table */
Alter table bbssection
Add constraint pk_sid primary key (SID), -- primary key
Constraint df_sclickcount default (0) for sclickcount, -- the default CTR is 0.
Constraint df_stopiccount default (0) for stopiccount, -- the default value of the number of senders is 0.
Constraint df_smasterid foreign key (smasterid) References bbsusers (UID) -- foreign key

/* Create a bbstopic table */
Use bbsdb
If exists (select * From sysobjects where name = 'bbstopic ')
Drop table bbstopic
Create Table bbstopic
(
TID int identity () not null, -- post number, automatic Growth
Tsid int not null, -- sender ID
Tuid int not null, -- Moderator user ID
Treplycount int null, -- number of replies
Tface int null, -- Post expression
Ttopic varchar (20) not null, -- title
Tcontents varchar (30) not null, -- body
TTime datetime null, -- posting time
Tclickcount int null, -- number of clicks
Tstate int not null, -- state
Tlastreply datetime null -- reply time
)

/* Constraints for creating a bbstopic table */
Alter table bbstopic
Add constraint df_treplycount default (0) for treplycount, -- the default number of replies is 0.
Constraint PK_tid primary key (tid), -- primary key
Constraint DF_TclickCount default (0) for TclickCount, -- the default number of clicks is 0.
Constraint DF_Tstate default (1) for Tstate, -- the default status is 1
Constraint DF_Ttime default (getdate () for Ttime, -- the default posting time is the system date.
Constraint CK_Tcontents check (len (Tcontents)> = 6), -- the body must be greater than six characters
Constraint CK_TlastReply check (TlastReply)> (Ttime), -- the last reply time must be later than the posting time
Constraint DF_TsID foreign key (TsID) references bbsSection (SID), -- foreign key
Constraint DF_TuID foreign key (TuID) references bbsUsers (UID) -- foreign key

/* Create a bbsReply table */
Use bbsdb
If exists (select * from sysobjects where name = 'bbsreply ')
Drop table bbsReply
Create table bbsReply
(
RID int identity () not null, -- automatic number, post number
RtID int not null, -- primary ID
RsID int not null, -- plate ID
RuID int not null, -- reply person ID
Rface int null, -- reply expression
Rcontents varchar (30) not null, -- body
Rtime datetime null, -- reply time
RclickCount int null -- number of clicks
)

/* Create constraints for the bbsReply table */
Alter table bbsReply
Add constraint DF_Rtime default (getdate () for Rtime, -- the default reply time is the system date.
Constraint CK_Rcontents check (len (Rcontents)> = 6), -- the body must be greater than six characters
Constraint DF_RtID foreign key (RtID) references bbsTopic (TID), -- foreign key
Constraint DF_RsID foreign key (RsID) references bbsSection (SID), -- foreign key
Constraint DF_RuID foreign key (RuID) references bbsUsers (UID) -- foreign key