如何创建含互斥1:1关系的SQL表并实现数据插入？

你的SQL执行失败核心有两个问题：

1. 尝试给vlasnik表添加外键约束时，表中不存在PRAVNA_OSOBA_ID和FIZICKA_OSOBA_ID字段，导致ALTER语句报错。
2. 未实现vlasnik与另外两个实体的互斥1:1关系，无法保证一个所有者只能是法人或自然人中的一种。

正确的表创建SQL #

根据ER图的互斥1:1关系，我们通过在vlasnik表中添加类型标识，配合子表约束来实现需求：

-- 创建所有者主表，添加类型字段区分法人/自然人
CREATE TABLE vlasnik (
    vlasnik_id INTEGER NOT NULL CONSTRAINT vlasnik_pk PRIMARY KEY,
    datum_zakupa DATE NOT NULL,
    tip_vlasnika CHAR(1) NOT NULL,
    -- 约束类型只能是P(法人)或F(自然人)
    CONSTRAINT chk_tip_vlasnika CHECK (tip_vlasnika IN ('P', 'F'))
);

-- 创建法人表，主键同时作为外键关联所有者，且仅允许关联类型为P的所有者
CREATE TABLE pravna_osoba (
    pravna_osoba_id INTEGER NOT NULL CONSTRAINT pravna_osoba_pk PRIMARY KEY,
    naziv VARCHAR2(20) NOT NULL,
    ime_ravnatelja VARCHAR2(20) NOT NULL,
    prezime_ravnatelja VARCHAR2(20) NOT NULL,
    datum_osnutka DATE NOT NULL,
    OIB_tvrtke VARCHAR2(13) NOT NULL,
    -- 外键关联所有者表
    CONSTRAINT fk_pravna_osoba_vlasnik FOREIGN KEY (pravna_osoba_id) REFERENCES vlasnik(vlasnik_id),
    -- 约束确保关联的所有者类型为P
    CONSTRAINT chk_pravna_osoba_tip CHECK (
        (SELECT tip_vlasnika FROM vlasnik WHERE vlasnik_id = pravna_osoba_id) = 'P'
    )
);

-- 创建自然人表，主键同时作为外键关联所有者，且仅允许关联类型为F的所有者
CREATE TABLE fizicka_osoba (
    fizicka_osoba_id INTEGER NOT NULL CONSTRAINT fizicka_osoba_pk PRIMARY KEY,
    ime VARCHAR2(20) NOT NULL,
    prezime VARCHAR2(20) NOT NULL,
    OIB VARCHAR2(13) NOT NULL,
    datum_rodenja DATE NOT NULL,
    primarna_djelatnost VARCHAR2(30) NOT NULL,
    broj_sticenika INTEGER NOT NULL,
    -- 外键关联所有者表
    CONSTRAINT fk_fizicka_osoba_vlasnik FOREIGN KEY (fizicka_osoba_id) REFERENCES vlasnik(vlasnik_id),
    -- 约束确保关联的所有者类型为F
    CONSTRAINT chk_fizicka_osoba_tip CHECK (
        (SELECT tip_vlasnika FROM vlasnik WHERE vlasnik_id = fizicka_osoba_id) = 'F'
    )
);

注：如果你的Oracle版本不支持行级子查询的CHECK约束，可改用触发器实现类型校验：

-- 法人表的类型校验触发器
CREATE OR REPLACE TRIGGER trg_pravna_osoba_tip
BEFORE INSERT OR UPDATE ON pravna_osoba
FOR EACH ROW
DECLARE
    v_tip vlasnik.tip_vlasnika%TYPE;
BEGIN
    SELECT tip_vlasnika INTO v_tip FROM vlasnik WHERE vlasnik_id = :NEW.pravna_osoba_id;
    IF v_tip != 'P' THEN
        RAISE_APPLICATION_ERROR(-20001, '法人只能关联类型为P的所有者');
    END IF;
END;
/

-- 自然人表的类型校验触发器
CREATE OR REPLACE TRIGGER trg_fizicka_osoba_tip
BEFORE INSERT OR UPDATE ON fizicka_osoba
FOR EACH ROW
DECLARE
    v_tip vlasnik.tip_vlasnika%TYPE;
BEGIN
    SELECT tip_vlasnika INTO v_tip FROM vlasnik WHERE vlasnik_id = :NEW.fizicka_osoba_id;
    IF v_tip != 'F' THEN
        RAISE_APPLICATION_ERROR(-20002, '自然人只能关联类型为F的所有者');
    END IF;
END;
/

数据插入示例 #

-- 插入法人类型的所有者
INSERT INTO vlasnik (vlasnik_id, datum_zakupa, tip_vlasnika) VALUES (1, TO_DATE('2024-01-01', 'YYYY-MM-DD'), 'P');
INSERT INTO pravna_osoba (pravna_osoba_id, naziv, ime_ravnatelja, prezime_ravnatelja, datum_osnutka, OIB_tvrtke)
VALUES (1, 'Tech d.o.o.', 'Ivan', 'Horvat', TO_DATE('2020-05-15', 'YYYY-MM-DD'), '1234567890123');

-- 插入自然人类型的所有者
INSERT INTO vlasnik (vlasnik_id, datum_zakupa, tip_vlasnika) VALUES (2, TO_DATE('2024-02-10', 'YYYY-MM-DD'), 'F');
INSERT INTO fizicka_osoba (fizicka_osoba_id, ime, prezime, OIB, datum_rodenja, primarna_djelatnost, broj_sticenika)
VALUES (2, 'Ana', 'Kovač', '9876543210987', TO_DATE('1985-03-20', 'YYYY-MM-DD'), 'Programer', 2);

内容的提问来源于stack exchange，提问作者Matea_my