先有雞還是先有蛋的情境,例如:
員工資料裡面有所屬部門,部門資料表裡有部門主管,部門主管同時也是員工一員.
會有這樣的設計:
create table empl (
eid int not null primary key
, ename text not null
, dept_id int not null
);
create table dept (
did int not null primary key
, dname text not null
, manager_id int not null
);
alter table dept
add foreign key (manager_id)
references empl(eid);
alter table empl
add foreign key (dept_id)
references dept(did);
此時要 insert 資料時,會有一些麻煩
Demo
不限制方法.
已邀請的邦友 {{ invite_list.length }}/5
我不會把主管設計在dept這表裡，員工的身分放在員工的表就好啦。
create table empl (
eid int not null primary key
, ename text not null
, dept_id int not null
, position int not null #數字，越大職位越高
, tmpposition int #臨時身分
);
create table dept (
did int not null primary key
, dname text not null
);
如果還是要放dept，因為實際上會有臨時代理及暫時沒有主管的狀況，至少 manager_id 我不會設為not null
Oracle可以同時新增不同資料表~
所以沒有外鍵問題~
create table empl (
eid number(10,0) PRIMARY KEY not null
, ename varchar2(50) not null
, dept_id number(10,0) not null
);
create table dept (
did number(10,0) primary key not null
, dname varchar2(50) not null
, manager_id number(10,0) not null
);
create table tmp (
id number(10,0) primary key not null
, name varchar2(50) not null
, reid number(10,0) not null
);
ALTER TABLE dept
ADD CONSTRAINT X1
FOREIGN KEY (manager_id) REFERENCES empl (eid)
INITIALLY DEFERRED DEFERRABLE;
ALTER TABLE empl
ADD CONSTRAINT X2
FOREIGN KEY (dept_id) REFERENCES dept(did)
INITIALLY DEFERRED DEFERRABLE;
insert all
into empl(eid,ename,dept_id) values(id,name,reid)
into dept(did,dname,manager_id) values(id,name,reid)
select * from tmp
select * from empl
select * from dept
https://dbfiddle.uk/?rdbms=oracle_21&fiddle=605154fcf34fb38f45a7af2c19fc604e
Oracle 有 DEFERRABLE
https://www.oreilly.com/library/view/sql-in-a/9780596155322/re42.html
原來如此~考慮到會被外鍵鎖住的問題~
不限方法的話...這樣算作弊嗎...(HeidiSQL)
Create完直接先insert，在將欄位的default都設為0
SET FOREIGN_KEY_CHECKS=0;
INSERT INTO dept VALUES ('0','待確認','0');
INSERT INTO empl VALUES ('0','待確認','0');
SET FOREIGN_KEY_CHECKS=1;
小孩才做選擇
雞蛋同時有
把酒問青天
with new_dept as (
insert into dept (did, dname, manager_id) values (1,'IT',101)
returning manager_id
)
insert into empl (eid,ename,dept_id)
values
( (select manager_id from new_dept),'Tom',1);
從這裡抄來改的
iThome鐵人賽
看影片追技術