英文:
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)
);
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论