I am creating tables to handle the security question/selected question/given answer section of our database and getting this error:
there is no unique constraint matching given keys for referenced table "m_security_questions"
Not sure how I fix this?
(Since schema won't build b/c of error, I couldn't add SQL Fiddle)
CREATE TABLE security_question --defines questions ( id SERIAL PRIMARY KEY NOT NULL, question character varying(1024) NOT NULL, is_custom boolean DEFAULT FALSE NOT NULL ); INSERT INTO security_question (question,is_custom) VALUES ('do you know the answer?',FALSE), ('Write your own question',TRUE); CREATE TABLE m_security_questions ( --defines question a member chooses & allows free form question -- id SERIAL NOT NULL, -- I know adding id like this and making keeping same pk solves it -- but isn't storing the extra sequence not needed? member integer --REFERENCES member(m_no) -- commented out reference for so only NOT NULL, question integer REFERENCES security_question(id) NOT NULL, m_note text, PRIMARY KEY(member,question) ); -- here I add unique constraint but doesn't the primary already mean I have a unique index? ALTER TABLE m_security_questions ADD CONSTRAINT m_security_questions_unique_member_question UNIQUE (member,question); INSERT INTO m_security_questions (member,question,m_note) VALUES (2,1,NULL), (2,2,'How many marbles in this jar?'); CREATE TABLE m_security_answer --defines members given answer ( -- I want member & question here to line up w/ same from m_security_questions member integer REFERENCES m_security_questions(member), question integer REFERENCES m_security_questions(question) NOT NULL, answer character varying(255) NOT NULL, PRIMARY KEY (member,question) ); -- here is where I get the error: -- there is no unique constraint matching given keys for referenced table "m_security_questions" INSERT INTO m_security_answer (member,question,answer) VALUES (2,1,'yes'), (2,2,'431'); 
