CODEWITHSUNDEEP
sqlpostgresqlsupabase
Mile
Mile

Reputation: 17

ERROR: syntax error at or near "FOREIGN" (SQLSTATE 42601)

I'm trying to add to a column that already exists, in an orders table, the constraint that it is a foring key:

ALTER TABLE IF EXISTS public.orders DROP COLUMN IF EXISTS location_id;

ALTER TABLE IF EXISTS public.orders
    ADD COLUMN location_id bigint
    CONSTRAINT orders_location_id_fkey FOREIGN KEY (location_id)
        REFERENCES public.locations (id) MATCH SIMPLE
        ON UPDATE NO ACTION
        ON DELETE NO ACTION

Upvotes: 1

Views: 1518

Answers (1)

Laurenz Albe
Laurenz Albe

Reputation: 247410

You cannot use the “table constraint” syntax in a column definition. Use the “column constraint” syntax:

... ADD COLUMN location_id bigint
        CONSTRAINT orders_location_id_fkey REFERENCES public.locations (id)

See the syntax diagram in the documentation:

ADD [ COLUMN ] [ IF NOT EXISTS ] column_name data_type [ COLLATE collation ] [ column_constraint [ ... ] ]
[...]
and column_constraint is:

[ CONSTRAINT constraint_name ]
[...]
REFERENCES reftable [ ( refcolumn ) ] [ MATCH FULL | MATCH PARTIAL | MATCH SIMPLE ]
[ ON DELETE referential_action ] [ ON UPDATE referential_action ] }

Upvotes: 1

Related Questions