Conflicted with the FOREIGN KEY SAME TABLE

Question

I have created a table named Produk

CREATE TABLE Produk
(
    IDProduk varchar(10) NOT NULL,
    NamaProduk varchar(50) NOT NULL,
    Ukuran int NOT NULL,
    IDKategori varchar(10) NOT NULL,
    HargaSatuan money NOT NULL,

    PRIMARY KEY (IDProduk),
    FOREIGN KEY (IDKategori) REFERENCES dbo.Produk(IDProduk)
);

And I'm trying to insert multiple data to this table with this command

INSERT INTO Produk (IDProduk, NamaProduk, IDKategori, HargaSatuan)
VALUES
('PRD0000001', 'Meja Makan', 'KTG0000001', 3000000),
('PRD0000002', 'Kursi Roda', 'KTG0000002', 2500000),
('PRD0000003', 'Meja Kantor', 'KTG0000001', 1000000),
('PRD0000004', 'Rak Piring', 'KTG0000003', 500000),
('PRD0000005', 'Rak Sepatu', 'KTG0000003', 200000)

But I'm getting this error

The INSERT statement conflicted with the FOREIGN KEY SAME TABLE constraint "FK_IDKategori". The conflict occurred in database "Tugas1", table "dbo.Produk", column 'IDProduk'

Thanks!

Answer 1

You have a foreign key to the primary key in the same table. But none of the values you try to insert has the field referenced by that foreign key have a value that's a primary key as well.

Which is why you're getting the fully expected error on insert.

Worse, the way you constructed the foreign key means you can now never insert anything in the table, as you can only insert something where the IDKategori field has a value that already exists in the IDProduk column. Obviously if you have nothing in the table nothing exists in that column so there's no way to insert anything.