CODEWITHSUNDEEP
postgresql
bilak
bilak

Reputation: 4942

PostgreSQL same name for constraint and primary key

migrating from Oracle I'd like to reuse some definitions like:

ALTER TABLE CLIENTUSERS
ADD CONSTRAINT UK_CLIENTUSERS_CLIENTUS UNIQUE (CLIENTID, USERID)
USING INDEX TABLESPACE IDX

However this fails because clause using index is missing index name. Problem is that when I specify UK_CLIENTUSERS_CLIENTUS it fails with [42P07] ERROR: relation "uk_clientusers_clientus" already exists

Do I need to generate another name for constraint or is it possible to reuse the name of index like oracle do?

Upvotes: 0

Views: 515

Answers (1)

Lukasz Szozda
Lukasz Szozda

Reputation: 175796

If you want to use USING INDEX syntax you should omit column list inside ALTER TABLE statement:

CREATE TABLE CLIENTUSERS(CLIENTID INT, USERID INT);

CREATE UNIQUE INDEX UK_CLIENTUSERS_CLIENTUS ON CLIENTUSERS(CLIENTID, USERID);

ALTER TABLE CLIENTUSERS
ADD CONSTRAINT UK_CLIENTUSERS_CLIENTUS UNIQUE -- here columns were removed
USING INDEX UK_CLIENTUSERS_CLIENTUS;

db<>fiddle demo

Upvotes: 2

Related Questions