数据库表创建

create table depm
(
 depm_id number(3) constraint dep_id_pk primary key,
 depm_name varchar(30) not null
)

create table emp2
(
 emp2_id number(4) constraint emp2_id_pk primary key,
 emp2_name varchar(20) not null,
 email varchar(40) not null,
 constraint emp2_email_uk unique(email),
 depm_id number(3) ,
 constraint fk_emp2_depm foreign key(depm_id) references depm(depm_id)
)

 

1、创建表
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;
2、创建主键
ALTER TABLE students
  ADD CONSTRAINT pk_students PRIMARY KEY (student_id)
  USING INDEX TABLESPACE student_index;
3、创建Unique约束
ALTER TABLE students
  ADD CONSTRAINT uk_students_license
  UNIQUE (state, license_no)
  USING INDEX TABLESPACE student_index;
4、创建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));
5、创建外键约束
ALTER TABLE students
  ADD CONSTRAINT fk_students_state
  FOREIGN KEY (state) REFERENCES state_lookup (state);