SQL Server build Library-Build table-build constraints

----------------------------------------SQL Server Build database-Build table-build constraints Create school databases--------------------------------------

--before creating the School database: first to determine if the database exists, if it exists, then create it if it does not exist--
--exists keyword: The parentheses inside can query to the data return ' true ' otherwise return ' false '
if exists (SELECT * from sysdatabases where name = ' School ')
Span style= "color: #ff0000;" >--exists returns ' true ' to perform a delete database operation--
Drop databases School

--exists back " False ' indicates that the database does not exist and creates the
Create database School
on primary
(
-- Primary database file--
name = ' School ',--main data file logical name
FileName = ' D:\project\School.mdf ',--main data file physical logical name
size = 5MB,-- Initial value size
MaxSize = 100MB,--Maximum size
FileGrowth = 15%--Data file growth
)
Log on
(
- -Log file--
name = ' School_log ',
filename = ' D:\project\School_log.ldf ',
size = 2MB,
filegrowth = 1MB
)
Go

----------------------------------------Create an employee database using T-SQL------------------------------------
Create Database employee
on primary
(
--Primary Data file--
name = ' Employee1 ',
filename = ' D:\project\employee1.mdf ',
size = 10MB,
filegrowth = 10%
),
(
--secondary data file--
name = ' Employee2 ',
filename = ' D:\project\employee2.ndf ',
size = 20MB,
MaxSize = 100MB,
FileGrowth = 1MB
)
Log on
(
--first log file--
name = ' Employee_log1 ',
filename = ' D:\project\employee_log1.ldf ',
size = 10MB,
filegrowth = 1MB
),
(
--Second log file--
name = ' employee_log2 ',
filename = ' D:\project\employee_log2.ldf ',
size = 10MB,
MaxSize = 50MB,
filegrowth = 1MB
)

---------------------------------query for database information that already exists---------------------------
SELECT * FROM sysdatabases

---------------------------------Delete a database------------------------------------
Drop Database School

---------------------------------Create a Student database table----------------------------
--1, select the operational database--
Use School
Go

--Judging whether the table exists--
if exists (select * from sysobjects where name = ' Student ')
drop table Student

--2, creating Tables---
CREATE TABLE Student
(
-- specific column name data type column characteristics (whether empty)--
Studentno int Identity (2,1) is not NULL,
Loginpwd nvarchar () NOT NULL,
Studentname nvarchar () NOT NULL,
Sex int NOT NULL,
Gradeid int NOT NULL,
Phone nvarchar () NOT NULL,
Borndate datetime NOT NULL,
Address nvarchar (255),
Email nvarchar (50),
Identitycard varchar (18)
)
Go

---View all database objects (database tables)---
SELECT * from sysobjects

drop table Student

----------------------Create a subject curriculum-------------------
-----1, determine whether the table exists, if any, delete and then create, if not exist directly create--------
if exists (select * from sysobjects where name = ' Subject ')
drop TABLE Subject

Use School
Go

---Create a subject curriculum--
CREATE TABLE subject
(
Subjectno int NOT null identity (.
Subjectname nvarchar (50),
Classhour int,
Gradeid int
)

----------------------------------------Create a result score table-------------------
-----1, determine whether the table exists, if any, delete and then create, if not exist directly create--------
if exists (select * from sysobjects where name = ' Result ')
drop table Result

Use School
Go

---Create a result score table--
CREATE TABLE Result
(
Studentno int NOT NULL,
Subjectno int NOT NULL,
Examdate Datetime NOT NULL,
Studentresult int NOT NULL
)

-----------------------------------------Create a grande grade table-------------------
-----1, determine whether the table exists, if any, delete and then create, if not exist directly create--------
if exists (select * from sysobjects where name = ' Grade ')
drop table Grade

Use School
Go

---Create a grande grade table-
CREATE TABLE Grade
(
Gradeid int NOT NULL,
Grandename nvarchar (50)
)

Add constraints-----------------------------------------T-SQL-------------------------
--Add a PRIMARY KEY constraint to Studentno---
ALTER TABLE Student
Add constraint Pk_stuno primary key (Studentno)

--Add a unique constraint to the identity card--
ALTER TABLE Student
Add constraint Uq_stuidcard unique (identitycard)

---Add default constraints to address addresses--
ALTER TABLE Student
Add constraint df_stuaddress default (' Address unknown ') for address

---delete address addresses default constraint---
ALTER TABLE Student
Drop constraint df_stuaddress

----------Date of birth add a check constraint--------
ALTER TABLE Student
Add constraint ck_stuborndate check (borndate > ' 1980-01-01 ')

---------establish the primary foreign key relationship with grand (grade table)--------

--1, add grade primary key (Operation grade)---
ALTER TABLE Grade
Add constraint Pk_graid primary key (Gradeid)

--2, add grade foreign key (Operation student)--
ALTER TABLE Student
Add constraint Fk_stugradeid foreign key (Gradeid) references Grade (Gradeid)

-------------------add constraints to the subject curriculum-----------------------

----Add a PRIMARY KEY constraint to the Subjectno column------
ALTER TABLE subject
Add constraint Pk_subid primary key (Subjectno)

------Add a non-null constraint to the course name subjectname;-----
-----with Nocheck: Data is already present without a check constraint-------
ALTER TABLE subject with NOCHECK
Add constraint ck_subname Check (subjectname is not null)

-----hours must be greater than 0-----
ALTER TABLE subject with NOCHECK
Add constraint ck_classhour check (classhour > 0)

-----Add a primary foreign key constraint to the grade grade table----
ALTER TABLE subject with NOCHECK
Add constraint Fk_gradeid foreign key (Gradeid)
References Grade (Gradeid)

----------Add a constraint to the result score table------------

-------Add multiple Constraints---------
ALTER TABLE Result
Add
Constraint Pk_no_subid_date primary KEY (Studentno,subjectno,examdate),
Constraint df_examdate Default (GETDATE ()) for Examdate,
Constraint ck_studentresult Check (studentresult between 0 and 100),
Constraint Fk_stuno foreign KEY (Studentno) references Student (STUDENTNO),
Constraint fk_subno foreign KEY (Subjectno) references Subject (SUBJECTNO)

--Delete multiple constraints--
ALTER TABLE Result
Drop constraint Pk_no_subid_date,fk_subno,fk_stuno,ck_studentresult,df_examdate

--------Change the data type of a column----------
ALTER TABLE Result
ALTER COLUMN Studentresult INT

SQL Server build Library-Build table-build constraints