Postgres - unique constraint

Question

I'm facing the following error when I try to create that second table and I can't figure out why. For the table patente I want to be able to have (idfuncionario, titulo) as primary keys. I already tried to put the UNIQUE constraint as "UNIQUE (idfuncionario, titulo) as suggested in other topics.

CREATE TABLE publicacao
(
idfuncionario CHAR(5) NOT NULL,
titulo        VARCHAR(50) NOT NULL,
data          TIMESTAMP NOT NULL,
PRIMARY KEY (idfuncionario, titulo),
FOREIGN KEY(idfuncionario) REFERENCES trabalha(idfuncionario)
);

CREATE TABLE patente
(
idfuncionario CHAR(5) NOT NULL,
titulo        VARCHAR(50) NOT NULL,
datafim       TIMESTAMP NOT NULL,
descricao     VARCHAR(100) NOT NULL,
PRIMARY KEY (idfuncionario, titulo),
FOREIGN KEY(idfuncionario) REFERENCES trabalha(idfuncionario),
FOREIGN KEY(titulo) REFERENCES publicacao(titulo)
);

The message error:

ERROR: there is no unique constraint matching given keys for referenced table "publicacao" SQL state: 42830

I'm using Postgres 9.4 on a Windows 8.1 64bits

David Faber · Accepted Answer · 2015-01-08 03:42:27Z

2

The foreign key referencing publicacao must refer to that table's primary key. So you want to do the following when creating patente:

CREATE TABLE patente
(
  idfuncionario CHAR(5) NOT NULL,
  titulo        VARCHAR(50) NOT NULL,
  datafim       TIMESTAMP NOT NULL,
  descricao     VARCHAR(100) NOT NULL,
  PRIMARY KEY (idfuncionario, titulo),
  FOREIGN KEY (idfuncionario) REFERENCES trabalha (idfuncionario),
  FOREIGN KEY (idfunctionario, titulo) REFERENCES publicacao (idfuncionario,titulo)
);

I think the foreign key on idfunctionario would be redundant in this case.

On the other hand, maybe when you created publicacao you intended for its primary key to include only the column titulo?

answered Jan 8, 2015 at 3:42

David Faber

12.5k2 gold badges33 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alessandro Resta Over a year ago

I couldn't solve using your hint. The title for PK change would work for now, but probably i would have some functionalities issues later on. I did some changes at design level so is working smoothly now. But I really appreciated your help. Thank you and have a nice day.

David Faber Over a year ago

Alessandro, I'm sorry if my answer wasn't helpful. The basic issue is that a foreign key must refer to a unique index (preferably the primary key) of the parent table - that is how referential integrity is preserved.

Collectives™ on Stack Overflow

Postgres - unique constraint

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related