Question 89321 in PostgreSQL Question posted in
The official documentation can be found here.

PostgreSQL: error with ALTER TABLE … ADD CONSTRAINT UNIQUE

I am trying to add a column and a new constraint to a simple table in Postgres. It’s balking on the keyword UNIQUE. I’ve gone back and forth between my sliver of code and the documentation for ALTER TABLE, but I just can’t see what is wrong:

mint=> ALTER TABLE objects 
mint->   ADD COLUMN IF NOT EXISTS obj_parent_id int REFERENCES obj_id, --NULL OK
mint->   ADD CONSTRAINT UNIQUE (friendly_id, obj_parent_id)
mint-> ;
ERROR:  syntax error at or near "UNIQUE"
LINE 3:   ADD CONSTRAINT UNIQUE (friendly_id, obj_parent_id)
                         ^

For reference, the existing table, as it is, was created by:

CREATE TABLE IF NOT EXISTS objects (                                                             
    obj_id integer NOT NULL GENERATED BY DEFAULT AS IDENTITY (START WITH 1000),                  
    obj_type VARCHAR NOT NULL,
    friendly_id VARCHAR,
    last_changed timestamp DEFAULT now(),                                                        
    PRIMARY KEY (obj_id),
    UNIQUE (obj_id, obj_type),  --required for foreign key in object_props
    UNIQUE (obj_type, friendly_id),
    FOREIGN KEY (obj_type) REFERENCES object_types(obj_type) ON UPDATE CASCADE                   
);

I imagine I’m overlooking something simple, but I’d sure appreciate a pointer.

Tags:

2

Answers


  1. 0

    I believe you just ned to provide a name for the unique constraint you are adding in this syntax.

    That’s how it looks here – https://www.postgresql.org/docs/current/sql-altertable.html.

    E.g. change your last line to:

    ADD CONSTRAINT friend_parent_unique UNIQUE (friendly_id, obj_parent_id)
    Login or Signup to reply.
  2. 0

    According to the syntax of ALTER TABLE, when you use the keyword CONSTRAINT you need to specify a name for the constraint, like

    ALTER TABLE objects ADD CONSTRAINT objects_friendly_id_obj_parent_id_key UNIQUE (friendly_id, obj_parent_id);

    However, if you want postgres to automatically generate the name of the constraint, you can also omit the keyword entirely and write just

    ALTER TABLE objects ADD UNIQUE (friendly_id, obj_parent_id);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search