SQL创建表依赖关系 [英] SQL Creating Table Dependencies
本文介绍了SQL创建表依赖关系的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
我一直坚持创建表。
这里是我坚持如何使代码在 loan_type <中工作的代码/ code>写办公室工作人员或非办公室工作人员
Here is the code I am stuck at how to make the code work in loan_type
to write office worker or non office worker
create table loaner
(
loan_id number(5) primary key,
loan_type VARCHAR2 (16),
loan_start_date date,
loan_end_date date,
)
create table office_worker
(
worker_id number(5) primary_key,
loan_id number(5) references loaner(loan_id),
worker_name varchar2(50)
)
create table nonoffice_worker
(
nonworker_id number(5) primary_key,
loan_id number(5) references loaner(loan_id),
nonworker_name varchar2(50)
);
commit;
推荐答案
您无法创建约束来检查现有的表格结构。常见的做法是这样的:
You can't create a constrain to check that with the existing table structures. A common way to do it is like this:
create table loaner (
loan_id number(5) primary key,
loan_type VARCHAR2 (16),
loan_start_date date,
loan_end_date date,
constraint loaner_uk unique (loan_id, loan_type)
);
create table office_worker (
worker_id number(5) primary_key,
loan_id number(5),
loan_type VARCHAR2 (16),
worker_name varchar2(50),
constraint office_worker_loaner_fk foreeign key (loan_id, loan_type) references loaner (loan_id, loan_type),
constraint office_worker_loan_type_chk check (loan_type = 'OFFICE')
);
create table nonoffice_worker (
nonworker_id number(5) primary_key,
loan_id number(5),
loan_type VARCHAR2 (16),
nonworker_name varchar2(50),
constraint nonoffice_worker_loaner_fk foreeign key (loan_id, loan_type) references loaner (loan_id, loan_type),
constraint nonoffice_worker_loan_type_chk check (loan_type = 'NONOFFICE')
);
即:
- 在第一个表的(load_id,loan_type)中创建一个冗余的UNIQUE约束。
- 在子类型表中添加loan_type并将外键基于(loan_id,loan_type)。
- 向每个子类型表添加检查约束,以确保使用正确的loan_type。
这篇关于SQL创建表依赖关系的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文