CODEWITHSUNDEEP
sqlpostgresqlforeign-keysprimary-key
Davide Sonno
Davide Sonno

Reputation: 11

Insert tuple under foreign key constraint

first question posted...

The database is in italian, only thing you should be aware of is that articolo = article, dettagli = details, codice = code/id. I apologize for the inconvenience

I have two tables and each one references to the other one trough the same ID. Here are thetables:

CREATE TABLE IF NOT EXISTS public.articolo
(
    idarticolo integer NOT NULL,
    categoria character varying(15) COLLATE pg_catalog."default" NOT NULL,
    marca character varying(25) COLLATE pg_catalog."default",
    sesso character(1) COLLATE pg_catalog."default" NOT NULL,
    CONSTRAINT articolo_pimary_key PRIMARY KEY (idarticolo),
    CONSTRAINT articolo_dettagli_articolo_foreign_key FOREIGN KEY (idarticolo)
        REFERENCES public.dettagli_articolo (codice_articolo) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE
)

and

CREATE TABLE IF NOT EXISTS public.dettagli_articolo
(
    codice_articolo integer NOT NULL,
    descrizione character varying COLLATE pg_catalog."default",
    prezzo double precision NOT NULL,
    sconto double precision NOT NULL,
    peso double precision NOT NULL,
    CONSTRAINT dettagli_articolo_primary_key PRIMARY KEY(codice_articolo),
    CONSTRAINT dettagli_articolo_articolo_foreign_key FOREIGN KEY (codice_articolo)
        REFERENCES public.articolo (idarticolo) MATCH SIMPLE
        ON UPDATE CASCADE ON DELETE CASCADE
)

I did this simply to be able to delete both of the tuples by deleting one of them on "articolo" or from "dettagli_articolo". I know that the problem could be solved by mergin the two tables into one, or simply without having the foreign key on "articolo" to "dettagli_articolo".

Btw when I try to add a new article into "articolo"

insert into articolo values(999,'','','')

it obviously says that i cannot cause on "dettagli_articolo" the foreign key gets violated cause the tuple with the new articleID isnt on the referenced table.

ERROR: ERROR: the INSERT or UPDATE on the "article" table violates the foreign key constraint "article_details_article_foreign_key"
DETAIL: The key (itemid)=(999) is not present in the table "item_details".

Is there a way to fix this without removing the f_key? Maybe something like when I try to insert a new tuple on "articolo", an empty tuple with only the code gets created on "dettagli_articolo" so that there actully is something to reference to... Maybe even a constraint for the f_key like "ON INSERT"?

I apologize for any misunderstending or bad english :)

I have tried using transactions, inseting both tuples and then commit the transaction but didn't work. I tried having one of them not a primary key, but having the UNIQUE constraint but still nothing. Sadly im new to SQL so i have no idea of other elements

Upvotes: 1

Views: 439

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247235

Create one or both of the foreign keys with the option DEFERRABLE INITIALLY DEFERRED. Then the constraint is not checked at the end of the statement, but at the end of the transaction. So you can do

BEGIN;  -- start transaction

INSERT INTO /* the table with the deferred foreign key constraint */
INSERT INTO /* the other table */

COMMIT;  -- the deferred constraint is checked

Upvotes: 1

Related Questions