students表结构:
teachers表结构
同理外键的约束名也可以不写。
CREATE TABLE students (
student_id NUMBER(5)
CONSTRAINT student_pk PRIMARY KEY,
monitor_id NUMBER(5),
name VARCHAR2(10) NOT NULL,
sex VARCHAR2(6)
CONSTRAINT sex_chk CHECK(sex IN ('男','女')),
dob DATE,
specialty VARCHAR2(10)
);
主键可以直接写 primary key 系统自动为其命名
select constraint_name from user_constraints where table_name = 'TSA';
这样建表也可以:constraion constraion_name 可省
CREATE TABLE students (
student_id NUMBER(5) PRIMARY KEY,
monitor_id NUMBER(5),
name VARCHAR2(10) NOT NULL,
sex VARCHAR2(6) CHECK(sex IN ('男','女')),
dob DATE,
specialty VARCHAR2(10)
);
departments表结构
CREATE TABLE departments(
department_id NUMBER(3)
CONSTRAINT department_pk PRIMARY KEY,
department_name VARCHAR2(8) NOT NULL,
address VARCHAR2(40)
);
teachers表结构
CREATE TABLE teachers (
teacher_id NUMBER(5)
CONSTRAINT teacher_pk PRIMARY KEY,
name VARCHAR2(8) NOT NULL,
title VARCHAR2(6),
hire_date DATE DEFAULT SYSDATE,
bonus NUMBER(7,2),
wage NUMBER(7,2),
department_id NUMBER(3)
CONSTRAINT teachers_fk_departments
REFERENCES departments(department_id )
);
同理外键的约束名也可以不写。