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

huangapple go评论166阅读模式
英文:

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 the 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 answer 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 answer 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 answer to others' 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 the referenced table 'Comment'." I've looked 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 is pretty large, so if needed, I'll post more code. Thanks for answering!

P.S. I know it would be easier to have some attributes like "CommentId" or "UserId," but unfortunately, the project requires it to be implemented this way.

英文:

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.

答案1

得分: 0

在创建Answer表之前,除非为您的comment表添加唯一约束,否则会防止出现此错误。您可以将其作为独立语句执行,如下所示:

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

或者您可以在创建表时更新表脚本以包含它:

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)
);
英文:

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

huangapple
  • 本文由 发表于 2020年1月6日 23:46:41
  • 转载请务必保留本文链接:https://go.coder-hub.com/59615030.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定