CREATE TABLE public.impiegato(
CF varchar NOT NULL,
codice_reparto int4 NOT NULL,
mansione varchar NULL,
CONSTRAINT impiegato_pkey PRIMARY KEY (CF),
CONSTRAINT impiegato_fkey FOREIGN KEY (codice_reparto) REFERENCES reparto(codice),
);
CREATE TABLE public.reparto(
codice int4 NOT NULL,
nome varchar NULL,
cf_responsabile varchar NULL,
nome_responsabile varchar NULL UNIQUE,
CONSTRAINT reparto_pkey PRIMARY KEY (codice),
CONSTRAINT reparto_cf_responsabile_fkey FOREIGN KEY (cf_responsabile) REFERENCES impiegato(CF)
);
When i run the sql code it tells me that the impiegato table doesn't exist. Can I run a foreign key on a primary key of another table?
The referenced table must exists when a foreign key is declared.
Since in your case the two tables reference each other, it's not possible to solve this by simply creating the right one first.
You have to create the first one (any of them) first without the foreign key constraint, create the second on and then add the foreign key constraint to the first one.
Something along the lines of:
CREATE TABLE public.impiegato
(cf varchar
NOT NULL,
codice_reparto int4
NOT NULL,
mansione varchar
NULL,
CONSTRAINT impiegato_pkey
PRIMARY KEY (cf));
CREATE TABLE public.reparto
(codice int4
NOT NULL,
nome varchar
NULL,
cf_responsabile varchar
NULL,
nome_responsabile varchar
NULL
UNIQUE,
CONSTRAINT reparto_pkey
PRIMARY KEY (codice),
CONSTRAINT reparto_cf_responsabile_fkey
FOREIGN KEY (cf_responsabile)
REFERENCES impiegato
(cf));
ALTER TABLE public.impiegato
ADD CONSTRAINT impiegato_fkey
FOREIGN KEY (codice_reparto)
REFERENCES reparto
(codice);
Unfortunately, you didn't tag your DBMS. From some details I guessed it might be Postgres and the code above hence is Postgres code. If you don't use Postgres, you might need to adapt the ALTER TABLE statement, they can differ between DBMS.
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments