SQL备忘

TABLE

创建表

CREATE TABLE students
(student_id    VARCHAR2(10) NOT NULL,
student_name  VARCHAR2(30) NOT NULL,
college_major VARCHAR2(15) NOT NULL,
status        VARCHAR2(20) NOT NULL,
state         VARCHAR2(2),
license_no    VARCHAR2(30)) TABLESPACE student_data;

创建主键

ALTER TABLE students
ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
USING INDEX TABLESPACE student_index;

创建Unique约束

ALTER TABLE students
ADD CONSTRAINT uk_students_license
UNIQUE (state, license_no)
USING INDEX TABLESPACE student_index;

创建Check约束

ALTER TABLE students
ADD CONSTRAINT ck_students_st_lic
CHECK ((state IS NULL AND license_no IS NULL) OR
(state IS NOT NULL AND license_no is NOT NULL));

创建外键约束

ALTER TABLE students
ADD CONSTRAINT fk_students_state
FOREIGN KEY (state) REFERENCES state_lookup (state);