scheme sql ankieta

Question

Cześć, mógłby ktoś stworzyć mi takie scheme, mam problem z unique i relacjami ;/

 

 

CREATE TABLE test.survey(
id_survey SERIAL NOT NULL,
title varchar,
data_start timestamp,
PRIMARY KEY(id_survey)
);

CREATE TABLE test.question(
id_question SERIAL NOT NULL,
question_text varchar,
PRIMARY KEY(id_question)
);

CREATE TABLE test.survey_question(
id_survey int NOT NULL,
id_question int NOT NULL,
PRIMARY KEY(id_survey, id_question),
FOREIGN KEY(id_survey) REFERENCES test.survey(id_survey),
FOREIGN KEY(id_question) REFERENCES test.question(id_question)
);

CREATE TABLE test.offered_answer(
id_offered_answer SERIAL NOT NULL,
answer_text varchar,
PRIMARY KEY(id_offered_answer)    
);

CREATE TABLE test.survey_question_answer(
id_survey int NOT NULL,
id_question int NOT NULL,
id_offered_answer int NOT NULL,
PRIMARY KEY(id_offered_answer, id_survey, id_question),
FOREIGN KEY (id_survey) REFERENCES test.survey_question(id_survey),
FOREIGN KEY (id_question) REFERENCES test.survey_question(id_survey)
);

 

przy ostatniej tabeli mam taki błąd:

ERROR:  there is no unique constraint matching given keys for referenced table "survey_question"

nie moge dodac unique bo wtedy bede mial sytuacje w ktorej musze miec 1 pytanie do 1 ankiety a chodzi mi o to , zeby bylo to uniwersalne ;/ staram sie odtworzyć ten schemat ale nie mam pojecia co robie zle ;/ prosze o pomoc