Set foreign key of a table to two target columns in same target table PostgreSQLl

I am creating a table "line" that has a "startpoint" column and "endpoint" column these columns should be filled by the name of these points, that is in the table "points" in column "point name". I am trying to set a foreign key from points table to line: one for the startpoint column and the other to endpoint column, but both referenced to the same column in points table called "point name".But it didn't work. How can I do it? Note that I am using Postgresql version 11.6 this the code :

'create table public.line(
    lineid integer NOT NULL DEFAULT nextval('line_lineid_seq'::regclass),
    linetype character varying(20) COLLATE pg_catalog."default",
    networkname character varying(50) COLLATE pg_catalog."default",
    startpointname character varying(20) COLLATE pg_catalog."default" NOT NULL,
    endpointname character varying(20) COLLATE pg_catalog."default",
    CONSTRAINT endpoint FOREIGN KEY (endpointname)
        REFERENCES public.points (pointsname) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT startpoint FOREIGN KEY (startpointname)
        REFERENCES public.points (pointsname) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION
        NOT VALID,
    CONSTRAINT length CHECK (length_m > 0::double precision),
    CONSTRAINT diameter CHECK (diameter_m > 0::double precision)
    );'

Thanks in advance

1 answer

  • answered 2020-01-18 10:55 GMB

    I am creating a table "line" that has a "startpoint" column and "endpoint" column

    [...]

    I am trying to set a foreign key from points table to line

    I think that you want things the other way around: you should have foreign keys in lines that reference the points table.

    Consider:

    create table points (
        point_id int primary key
    );
    
    create table lines (
        line_id int primary key,
        startpoint int references points(point_id),
        endpoint int references points(point_id)
    )