Nasty sql error(POSTGRES) “There is no unique constraint matching given keys for referenced table…”

EDIT: tables have been translated from my language to english i’ve changed te reference of foreign key in table Comment to “Utente” -> “User”

Hi everyone i’m trying to build a small review system in sql as follows. Every user can comment on some item(could be anything) and any other user can answer any comment. The Admin of the website cannot post comments itself, he/she can only answers to user’s comments… Here’s the code explained step by step.

Tables to define entities “User” and “Comment”

create table User(
  Username varchar(15),
  Email varchar (25),
  Nome varchar (25),
  Foto varchar (35),
  primary key (Username, Email) 
);

create table Comment (
  Username varchar(15),
  Email varchar(25),
  DateComment timestamp not null,
  Text varchar(250),    
  primary key(DateComment, Text, Email, Username),
  foreign key(Username, Email) references User(Username, Email)
        On update cascade
        On delete set null
);

Every User can post Comments on specific items and any user can also answers Comments like this:

create table Answer
(
   AnswerText varchar(500),
   DateAndTime timestamp not null,
   DateComment timestamp not null,
   Text varchar(500),
   Username varchar(15),
   Email varchar (25),
   primary key(AnswerText, DateAndTime, DateComment, Text, Username, Email),
    foreign key(DateComment, Text) references Comment(DateComment, Text)
        On update cascade
        On delete set null,
    foreign key(Username, Email) references User(Username, Email)
        On update cascade
        On delete set null
);

The admin of the website cannot post comments itself, he/she can only answers to other’s comments so i’ve created these tables

create table Admin(
    AdminName varchar(20) primary key

);

create table AdminAnswers
(
    DataComment timestamp not null,
    Text varchar(500),
    DateAndTime timestamp not null,
    AdminName varchar(20) unique,
    primary key(DataComment, Text, DateAndTime, AdminName),
    foreign key (DateComment, Text) references Comment(DateComment, Text)
        On update cascade
        On delete no action,
    foreign key(DateAndTime) references Answer(DateAndTime)
        On update cascade
        On delete no action,
        foreign key (AdminName) references Admin(AdminName) 
        On update no action
        On delete no action

);

Postgres says “There is no unique constraint matching keys for referenced table ‘Comment'”… I’ve look at some answers and i’ve tried to add ‘unique’ to the attributes of my table ‘User’, but i can’t get over it. The ddl file in pretty large so if needed i’ll post more code. Thanks for answering!

Ps. i know it would be easier to have some attributes like “CommentId” or “UserId” but, unfortunately the project requires to be implented this way.

Answer

Typos excluding adding a unique constraint to your comment table before the Answer table is created will prevent this error. You can do this as a standalone statement such as below:

alter table comment add constraint unique_for_fk unique(DateComment, Text);

Or you can update the table script to include it at time of the table creation:

create table Comment (
  Username varchar(15),
  Email varchar(25),
  DateComment timestamp not null,
  Text varchar(250),    
  primary key(DateComment, Text, Email, Username),
  foreign key(Username, Email) references "User"(Username, Email)
        On update cascade
        On delete set null,
  CONSTRAINT unique_for_fk UNIQUE( DateComment, Text)
);

Advertisement

Answer