Bagaimana seseorang bisa memastikan kombinasi dua kolom yang ada di tabel lain?

CREATE TABLE a (
    aid          INTEGER      PRIMARY KEY
);

CREATE TABLE b (
    aid          INTEGER      REFERENCES    a(aid),
    bid          INTEGER,
    PRIMARY KEY  (aid, bid)
);

CREATE TABLE c (
    aid          INTEGER      REFERENCES    a(aid),
    cid          INTEGER,
    PRIMARY KEY  (aid, cid)
);

-- combination of (aid, bid) must come from b
-- combination of (aid, cid) must come from c
CREATE TABLE d (
    aid          INTEGER      REFERENCES    a(aid),
    bid          INTEGER      REFERENCES    b(bid),
    cid          INTEGER      REFERENCES    c(bid),
    PRIMARY KEY  (aid, bid)
);

INSERT INTO a values(1);
INSERT INTO a values(2);
INSERT INTO b values(1, 1);
INSERT INTO b values(2, 2);
INSERT INTO c values(1, 1);
INSERT INTO c values(2, 2);
INSERT INTO d values(1, 2, 2);

Jelas pengkodean "CREATE TABLE d" di atas gagal memastikan

  • kombinasi (bantuan, tawaran) harus berasal dari b
  • kombinasi (bantuan, cid) harus berasal dari c

Terima kasih.

sql
3
sofname 23 Desember 2020, 23:06

3 jawaban

Jawaban Terbaik

Anda tidak dapat mendefinisikan batasan kunci asing multikolom sebaris. Definisikan mereka sendiri.

CREATE TABLE d
             (aid integer,
              bid integer,
              cid integer,
              PRIMARY KEY (aid,
                           bid),
              FOREIGN KEY (aid,
                           bid) REFERENCES b (aid,
                                              bid),
              FOREIGN KEY (aid,
                           cid) REFERENCES c (aid,
                                              cid));
1
sticky bit 23 Desember 2020, 20:12

Anda tidak menentukan RDBMS apa yang Anda gunakan. Di SQL Server Anda dapat membuat kunci asing menggunakan lebih dari satu kolom. Kunci tersebut harus mengarah ke kolom yang berada dalam kunci unik atau kunci utama dan Anda memiliki kunci utama yang diperlukan dalam tabel B dan C.

Definisi tabel A, B dan C dapat tetap seperti pada pertanyaan.

Definisi tabel D akan terlihat seperti ini dalam sintaks SQL Server. Di sini batasan kunci asing dibuat menggunakan pernyataan ALTER TABLE terpisah. DBMS yang berbeda mungkin menggunakan sintaks yang agak berbeda.

CREATE TABLE [dbo].[d](
    [aid] [int] NULL,
    [bid] [int] NULL,
    [cid] [int] NULL
) 
GO

ALTER TABLE [dbo].[d]  WITH CHECK ADD  CONSTRAINT [FK_d_b] FOREIGN KEY([aid], [bid])
REFERENCES [dbo].[b] ([aid], [bid])
GO

ALTER TABLE [dbo].[d] CHECK CONSTRAINT [FK_d_b]
GO

ALTER TABLE [dbo].[d]  WITH CHECK ADD  CONSTRAINT [FK_d_c] FOREIGN KEY([aid], [cid])
REFERENCES [dbo].[c] ([aid], [cid])
GO

ALTER TABLE [dbo].[d] CHECK CONSTRAINT [FK_d_c]
GO

Ini berfungsi seperti yang diharapkan dalam pengujian saya:

INSERT INTO a values(1);
INSERT INTO a values(2);
INSERT INTO b values(1, 1);
INSERT INTO b values(2, 2);
INSERT INTO c values(1, 1);
INSERT INTO c values(2, 2);


(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

(1 row affected)

Sekarang mari kita coba masukkan ke D:

INSERT INTO d values(1, 2, 2);

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK_d_b". 
The conflict occurred in database "TestDB", table "dbo.b".
The statement has been terminated.

Sisipan ini melalui, seperti yang diharapkan:

INSERT INTO d values(1, 1, 1);
INSERT INTO d values(2, 2, 2);

(1 row affected)

(1 row affected)

ATAU Anda dapat menggunakan sintaks ini:

CREATE TABLE d2 (
    aid          INTEGER,
    bid          INTEGER,
    cid          INTEGER,
    FOREIGN KEY (aid,bid) REFERENCES b (aid,bid),
    FOREIGN KEY (aid,cid) REFERENCES c (aid,cid)
);

Dalam hal ini kunci asing akan mendapatkan beberapa nama yang dibuat secara otomatis, tetapi mereka akan bekerja dengan cara yang sama:

INSERT INTO d2 values(1, 2, 2);

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__d2__351DDF8C".
The conflict occurred in database "TestDB", table "dbo.b".
The statement has been terminated.



INSERT INTO d2 values(1, 1, 2);

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__d2__361203C5".
The conflict occurred in database "TestDB", table "dbo.c".
The statement has been terminated.

Sisipan ini berfungsi, seperti yang diharapkan:

INSERT INTO d2 values(1, 1, 1);
INSERT INTO d2 values(2, 2, 2);


(1 row affected)

(1 row affected)

ATAU, Anda dapat menggunakan sintaks ini untuk memberi batasan beberapa nama yang bermakna:

CREATE TABLE d3 (
    aid          INTEGER,
    bid          INTEGER,
    cid          INTEGER,
    CONSTRAINT FK_d3_b FOREIGN KEY (aid,bid) REFERENCES b (aid,bid),
    CONSTRAINT FK_d3_c FOREIGN KEY (aid,cid) REFERENCES c (aid,cid)
);
1
Vladimir Baranov 29 Desember 2020, 08:44