ch11 integrity.sql

以Microsoft SQL Server为代表的数据库产品具有超大容量的数据存储、高效率的数据查询算法、方便易用的向导和工具及友好亲切的用户接口

 
USE AdventureWorks
SELECT *
FROM sys.key_constraints


 


USE ElecTravelCom
CREATE TABLE students(
studentID INT,
studentName VARCHAR(60),
gender CHAR(1) DEFAULT 'M',
birthdate DATETIME,
SSL CHAR(18))

DROP TABLE students




SELECT *
FROM students


USE ElecTravelCom
ALTER TABLE students 
ADD CONSTRAINT DEFAULT_student_birthdate DEFAULT '1990-01-01' FOR birthdate
GO



ALTER TABLE students DROP CONSTRAINT DEFAULT_student_birthdate
USE ETravelCom
DROP TABLE students



USE ElecTravelCom
CREATE TABLE students(
studentID INT,
studentName VARCHAR(20),
gender CHAR(1) DEFAULT 'M' CHECK (gender = 'F' OR gender = 'M'),
birthdate DATETIME,
SSL CHAR(18))






USE ElecTravelCom
ALTER TABLE students 
ADD CHECK (birthdate >= '1980-01-01' AND birthdate <= '1990-12-31') 
GO


DROP TABLE students

USE ElecTravelCom
CREATE TABLE students(
studentID INT PRIMARY KEY,
studentName VARCHAR(60),
gender CHAR(1) DEFAULT 'M' CHECK (gender = 'F' OR gender = 'M'),
birthdate DATETIME,
SSL CHAR(18))





USE ElecTravelCom
CREATE TABLE nationalStudents(
universityID INT NOT NULL,
studentID INT NOT NULL,
PRIMARY KEY (universityID, studentID),
studentName VARCHAR(60),
gender CHAR(1),
birthdate DATETIME,
SSL CHAR(18))










USE ElecTravelCom
ALTER TABLE students
ADD UNIQUE (SSL)
GO





USE ElecTravelCom
CREATE TABLE borrowBooks(
serialID INT IDENTITY PRIMARY KEY,
studentID INT,
borrowDate DATETIME,
bookID VARCHAR(32))



DROP TABLE borrowBooks


USE ElecTravelCom
ALTER TABLE borrowBooks
ADD CONSTRAINT FK_borrowBooks_sID_A6D 
FOREIGN KEY (studentID)
REFERENCES students(studentID)
ON DELETE CASCADE





USE ElecTravelCom
ALTER TABLE borrowBooks
DROP CONSTRAINT FK_borrowBooks_sID_A6D 
GO






USE ElecTravelCom
CREATE TABLE employees(
emp_num int NOT NULL CONSTRAINT PK_emp_num PRIMARY KEY,
mgr_num int NOT NULL CONSTRAINT FK_mgr_num FOREIGN KEY REFERENCES employees(emp_num))
GO



SELECT *
FROM students


USE ElecTravelCom
ALTER TABLE students
WITH NOCHECK
ADD CONSTRAINT CK_students_birthdate CHECK (birthdate <> '1985-05-1')
GO



USE ElecTravelCom
ALTER TABLE students
NOCHECK CONSTRAINT CK_students_birthdate
GO
ALTER TABLE students
CHECK CONSTRAINT CK_students_birthdate
GO