Nasty sql error(POSTGRES) "There is no unique constraint matching given keys for referenced table..."

Question

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.

Match fields between your definitions of Primary key for Comment and Foreign key that references it. — PM 77-1, Jan 06 '20 at 15:54

VynlJunkie · Accepted Answer · 2020-01-06T17:08:05.027

0

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)
);

edited Jan 06 '20 at 17:08

answered Jan 06 '20 at 16:03

VynlJunkie

1,953
22
26

The table Users is, practice called 'Utente', and yes 'Utente' is a typo(i've translated all tables to english to make them more understandable)... Anyway can i simply add unique(DateComment, Text) to my ddl file? – flatbrain Jan 06 '20 at 16:08
if you add it before you create a table that depends on it, yes – VynlJunkie Jan 06 '20 at 16:12
i'm definetly not getting it... would you please write an example of how my ddl file would look after adding that constraint? – flatbrain Jan 06 '20 at 16:19
are you creating this from a script, or executing the statements as listed in your original post ? – VynlJunkie Jan 06 '20 at 16:44
i'm exeucting a .sql file inside the postgres shell... the tables are listed as above thanks – flatbrain Jan 06 '20 at 16:50
edited my response to hopefully be more understandable – VynlJunkie Jan 06 '20 at 17:08

Nasty sql error(POSTGRES) "There is no unique constraint matching given keys for referenced table..."

1 Answers1