PostgreSQL: Foreign Key to a table with 3 Primary Keys on PgAdmin

Question

I have a table called table2 whitch has 3 PK, categoryid, languageid & unitid. I´m trying to make a FK from table1 to table2. But I cant add a FK constraint becouse of the next error:

On english: "ERROR there´s not an unique restriction whitch coincides with the columns taken on units table"

Table 2:

Table 1:

Columns

Constraints:

On PSQL

table 2 (units)

                              Tabla ½public.units╗
       Columna       |       Tipo       | Ordenamiento | Nulable  | Por omisi¾n
---------------------+------------------+--------------+----------+-------------
 unitdisplayname     | text             |              |          |
 unitdescription     | text             |              |          |
 unitid              | integer          |              | not null |
 conversionfactor    | double precision |              |          |
 typesystem          | text             |              |          |
 prefixfactor        | integer          |              |          |
 categorydisplayname | text             |              |          |
 categoryid          | integer          |              | not null |
 languageid          | text             |              | not null |
═ndices:
    "units_pkey" PRIMARY KEY, btree (unitid, categoryid, languageid)

The SQL Sentence im trying:

CREATE TABLE public.units_sensores
(
    id_rel integer NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 ),
    obra integer NOT NULL,
    edificio_serverguid integer NOT NULL,
    categoryid integer NOT NULL,
    unitid integer NOT NULL,
    PRIMARY KEY (id_rel),
    CONSTRAINT obras FOREIGN KEY (obra)
        REFERENCES public.obras (id_obra) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT edificios_serverguid FOREIGN KEY (edificio_serverguid)
        REFERENCES public.edificios_trend_meta (id_edificio) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT categoryid FOREIGN KEY (categoryid)
        REFERENCES public.units (categoryid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION,
    CONSTRAINT unitid FOREIGN KEY (unitid)
        REFERENCES public.units (unitid) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
)
WITH (
    OIDS = FALSE
);

ALTER TABLE public.units_sensores
    OWNER to postgres;

SUMMARY:

I´d like to know how to write this to postgreSQL:

reate Table A (
    A_ID char(3) primary key,
    A_name char(10) primary key,
    A_desc desc char(50)
)

create Table B (
    B_ID char(3) primary key,
    B_A_ID char(3),
    B_A_Name char(10),
    constraint [Fk_B_01] foreign key (B_A_ID,B_A_Name) references A(A_ID,A_Name)
)

Answer 1

You cannot create that foreign key constraint because there is no unique constraint on units(category_id).

For a foreign key you need a PRIMARY KEY or UNIQUE constraint that is defined on exactly the target columns. This constraint identifies the target row.

A unique constraint that encompasses additional columns is not good enough, since there still could be more than one row in the target table with the same category_id.

Since a table can only have a single primary key constraint, you have to use UNIQUE constraints. To get the same effect as with a primary key, the column should be NOT NULL too (that is already the case in your table).

Example:

ALTER TABLE units
   ADD UNIQUE (category_id);

Adding these constraints may make the primary key superfluous. In that case, you could drop it and make one of the new constraints the primary key.