Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2007
    Posts
    2

    Question Unanswered: (many-to-many)-to-many -> there is no unique constraint matching given keys for refe

    Hi,

    I designed a database schema and now Postgresql does not like it.
    Here is what I like to do:

    This is my first (simple) many-to-many relation.
    Code:
    --
    --  [ uri ]>----------------------<[ person ]
    --
    --                    ||
    --                    \/
    --
    --  [ uri ]-----<[ bookmark ]>-----[ person ]
    --
    In SQL it looks like this (in my opinion):
    Code:
    CREATE TABLE uri(
        hash CHAR(32) NOT NULL,
        uri VARCHAR(256) NOT NULL,
        PRIMARY KEY(hash)
    );
    
    CREATE TABLE person(
        name VARCHAR(16) NOT NULL,
        email VARCHAR(256), -- email is optional!
        password CHAR(32) NOT NULL,
        PRIMARY KEY(name)
    );
    
    CREATE TABLE bookmark(
        name VARCHAR(16) NOT NULL REFERENCES person(name) ON UPDATE CASCADE ON DELETE CASCADE,
        hash CHAR(32)    NOT NULL REFERENCES uri(hash)    ON UPDATE CASCADE ON DELETE CASCADE,
        title VARCHAR(64) NOT NULL,
        note TEXT,
        private BOOLEAN NOT NULL DEFAULT FALSE,
        PRIMARY KEY(name, hash)
    );
    So far everything works is fine.

    Now we come to the difficult part (at least for me):
    • Every bookmark has many tags.
    • Every tag has many bookmarks.

    Am I right?

    So it will look like this:
    Code:
    --
    --  [ uri ]-----<[ bookmark ]>-----[ person ]        [ uri ]-----<[ bookmark ]>-----[ person ]
    --                    V                                                |
    --                    |                         --\                    ^
    --                    |                         --/              [ bookmark_tag ]
    --                    ^                                                V
    --                 [ tag ]                                             |
    --                                                                  [ tag ]
    --
    My SQL code for the ERD above looks like this:
    Code:
    CREATE TABLE tag(
        label VARCHAR(32) NOT NULL,
        PRIMARY KEY(label)
    );
    
    CREATE TABLE bookmark_tag(
        name  VARCHAR(16) NOT NULL REFERENCES person(name) ON UPDATE CASCADE ON DELETE CASCADE,
        hash     CHAR(32) NOT NULL REFERENCES bookmark(hash) ON UPDATE CASCADE ON DELETE CASCADE,
        label VARCHAR(32) NOT NULL REFERENCES tag(label)     ON UPDATE CASCADE ON DELETE CASCADE,
        PRIMARY KEY(hash, label)
    );
    But as soon as I try to create the "bookmark_tag" table I get this error message from Postgresql:

    NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "bookmark_tag_pkey" for table "bookmark_tag"
    ERROR: there is no unique constraint matching given keys for referenced table "bookmark"

    That's the part where you could help me.
    How can I avoid this error? What I have to change in my design/SQL statements?

    I look forward to hearing from you.

    Yours sincerely
    Johannes

    Edit: I am terrible sorry. I just recognized that I eventually posted this topic unintended in the wrong forum.
    Last edited by 61qofke7lq; 02-11-07 at 15:38.

  2. #2
    Join Date
    Nov 2006
    Posts
    82
    IMHO the problem is that if u write
    hash CHAR(32) NOT NULL REFERENCES bookmark(hash) ON UPDATE CASCADE ON DELETE CASCADE,
    postgres will look for PK called 'hash' in table bookmark, but
    u don't have such primary key (there is a complex PK (hash, name) ). Maybe u should try to reorganize u'r database.

    (sorry for my poor english)

  3. #3
    Join Date
    Feb 2007
    Posts
    2
    Thanks for your reply and help.

    I just recognized, that I forgot to include the name as primary key at the table bookmark_tag.
    Yesterday evening I tweaked every create table statment except the bookmark_tag one.
    I guess thats it, at least it seems to work now (will test it now).

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •