在创建SQL表期间还有其他方法可以创建约束吗? [英] Is there any other way to create constraints during SQL table creation?

问题描述

假设我使用Oracle使用以下SQL语句:

Assume I have this following SQL statements, using Oracle:

drop table department cascade constraints;
drop table facultyStaff cascade constraints;
drop table student cascade constraints;
drop table campusClub cascade constraints;
drop table studentClub cascade constraints;

create table department 
(   code      varchar2(3) primary key,
    name      varchar2(40)  not null,
    chair     varchar2(11));

create table facultyStaff 
(   staffID     varchar2(5) primary key,
    dob     date,
    firstName   varchar2(20),
    lastName    varchar2(20),
    rank        varchar2(10),
    deptCode    varchar2(3),
    constraint rankValue check (rank in ('Assistant', 'Associate', 'Full', 'Emeritus')),
    constraint facultyDeptFk foreign key (deptCode) references department (code));

create table student 
(   studentId   varchar2(5) primary key,
    dob     date ,
    firstName   varchar2(20),
    lastName    varchar2(20),
    status      varchar(10),
    major       varchar(3),
constraint statusValue check (status in ('Freshman', 'Sophomore', 'Junior', 'Senior',  'Graduate')),
    constraint studentMajorFk foreign key (major) references department (code));

alter table department 
    add constraint departmentChairFk foreign key(chair) references facultyStaff(staffId) on delete set null; 

由于部门与教职员工之间存在递归关系，因此在定义教职员工表之前，无法定义部门中教席的外键约束.在使用alter table语句定义了教员之后，必须添加约束.

Since there is a recursive reference between department and faculty for the chair relationship, the foreign key constraint on chair in department cannot be defined until the faculty table is defined. The constraint must be added after faculty is defined using the alter table statement.

还有其他方法可以以某种方式自动执行约束创建，从而消除alter table语句吗?

Are there any other ways to do this, to somehow automate the constraint creation thus eliminating the alter table statement?

推荐答案

您可以使用创建架构语句:

create schema authorization [schema name]
create table department 
(   code      varchar2(3) primary key,
    name      varchar2(40)  not null,
    chair     varchar2(11),
    constraint departmentChairFk foreign key(chair) references facultyStaff(staffId) on delete set null
)
create table facultyStaff 
(   staffID     varchar2(5) primary key,
    dob     date,
    firstName   varchar2(20),
    lastName    varchar2(20),
    rank        varchar2(10),
    deptCode    varchar2(3),
    constraint rankValue check (rank in ('Assistant', 'Associate', 'Full', 'Emeritus')),
    constraint facultyDeptFk foreign key (deptCode) references department (code)
)
create table student 
(   studentId   varchar2(5) primary key,
    dob     date ,
    firstName   varchar2(20),
    lastName    varchar2(20),
    status      varchar(10),
    major       varchar(3),
constraint statusValue check (status in ('Freshman', 'Sophomore', 'Junior', 'Senior',  'Graduate')),
    constraint studentMajorFk foreign key (major) references department (code)
);

这篇关于在创建SQL表期间还有其他方法可以创建约束吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！