先創(chuàng)建一個(gè)表，表示學(xué)習(xí)的專業(yè)：
Create TABLE major
(ID number(3),
majorName nvarchar2(30));
然后對這個(gè)表增加各種約束
ALTER TABLE major ADD CONSTRAINTS pk_major PRIMARY KEY(ID); --主鍵約束
ALTER TABLE major MODIFY majorName CONSTRAINTS nn_majorName NOT NULL; --非空約束
ALTER TABLE major ADD CONSTRAINTS ck_majorID CHECK(ID<200); --Check約束
ALTER TABLE major MODIFY majorName CONSTRAINTS un_majorName UNIQUE; --唯一約束
重命名Constraint
ALTER TABLE major RENAME CONSTRAINT un_majorName TO un_major;
再創(chuàng)建一個(gè)學(xué)生表，在創(chuàng)建的同時(shí)增加各種約束
Create TABLE student
(studentID number(10) CONSTRAINTS pk_student PRIMARY KEY, --主鍵約束
ID number(20) CONSTRAINTS un_id UNIQUE,--唯一約束
studentName nvarchar2(30) CONSTRAINTS nn_name NOT NULL, --非空約束
majorId number(3) CONSTRAINTS fk_major_student REFERENCES major(ID) ON DELETE CASCADE,--外鍵約束
sex nvarchar2(1) DEFAULT 'M' CONSTRAINTS nn_sex NOT NULL,--默認(rèn)值,非空約束
CONSTRAINTS ck_sex CHECK((sex='M') OR (sex='F')), --Check約束
birthDate date,
birthPlace number(6));
將外鍵刪除
ALTER TABLE student DROP CONSTRAINTS fk_major_student;
然后添加外鍵
ALTER TABLE student ADD CONSTRAINTS fk_major_student FOREIGN KEY(majorID)
REFERENCES major(ID) ON DELETE CASCADE;
然后演示將所有約束刪除：
ALTER TABLE student DROP CONSTRAINTS pk_student ;
ALTER TABLE student DROP CONSTRAINTS un_id ;
ALTER TABLE student DROP CONSTRAINTS nn_name ;
ALTER TABLE student DROP CONSTRAINTS fk_major_student;
ALTER TABLE student DROP CONSTRAINTS nn_sex;
ALTER TABLE student DROP CONSTRAINTS ck_sex;