postgresql unique constraint not unique enough

Question

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

Magnus Hagander · Accepted Answer · 2013-08-16 17:11:27Z

2

The primary key definitely defines a unique constraint. But the unique constraint is on (member,question). Your have two FOREIGN KEY constraints that references just (member) and (question) separately.

I'm pretty sure what you want is:

CREATE TABLE m_security_answer --defines members given answer
  (
    member integer,
    question integer NOT NULL,
    answer character varying(255) NOT NULL,
    PRIMARY KEY (member,question),
    FOREIGN KEY (member, question) REFERENCES m_security_questions(member, question)
  );

answered Aug 16, 2013 at 17:11

Magnus Hagander

25.5k6 gold badges60 silver badges44 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

gooddadmike Over a year ago

I think you are correct. 4 minute restriction on correct answer. lol. Would you do it this way or just add a new id on m_security_questions (mentioned in edit)? Which is best?

Magnus Hagander Over a year ago

Nothing wrong with a two-column foreign key. You'll probably want to watch out for the column missing a NOT NULL constraint. NULLs vs FOREIGN KEYS can sometimes surprise. And AFAICT it's NOT NULL in one of the tables and NULL allowed in the other.

gooddadmike Over a year ago

I saw that shortly after I posted this. Both are meant to be NOT NULL.

Collectives™ on Stack Overflow

postgresql unique constraint not unique enough

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related