ch09.sql

SQLServer2005基础教程,清华大学出版社

USE AdventureWorks
SELECT *
FROM sys.key_constraints
 

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

DROP TABLE students

SELECT *
FROM students

USE ETravelCom
ALTER TABLE students 
ADD CONSTRAINT DEFAULT_student_birthdate DEFAULT '1980-01-01' FOR birthdate

 WITH VALUES


ALTER TABLE students DROP CONSTRAINT DEFAULT_student_birthdate
USE ETravelCom
DROP TABLE students

USE ETravelCom
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 ETravelCom
ALTER TABLE students 
ADD CHECK (birthdate >= '1980-12-01' AND birthdate <= '1990-12-01') 


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

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






USE ETravelCom
ALTER TABLE students
ADD UNIQUE (SSL)



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

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

USE ETravelCom
ALTER TABLE borrowBooks
DROP CONSTRAINT FK_borrowBooks_sID_A6D 



USE ETravelCom
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))


SELECT *
FROM students

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

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