0

I am facing the following problem: I need to create some kind of bill management system. There are bills and there are articles on said bills. Articles can be changed at a later date but they have to remain the way they were on bills that were already made. So far I was going with something like this:

CREATE TABLE article (
    article_id INTEGER NOT NULL,
    revision INTEGER NOT NULL,
    PRIMARY KEY (article_id, revision)
    );

CREATE TABLE bill (
    bill_id INTEGER PRIMARY KEY
    );

I went with an revision attribute to implement this. Every time an article gets updated, I'm going to create a new entry with the same id but with an incremented revision number. I also have a third table that references both the article and bill table. A bill can have multiple articles on it, but it shouldn't have more than one of the same article on it (so basically, bill 1 can have article A rev 0 and B rev 1 on it, but it can't have article A rev 0 and A rev 1 on it). I tried it this way:

CREATE TABLE contained (
    bill_id INTEGER REFERENCES bill(bill_id),
    FOREIGN KEY (article_id, revision) REFERENCES article(article_id, revision),
    PRIMARY KEY (bill_id, article_id)
    );

but it's obviously not working since I can't only use part of the referenced composite key in my primary key. Is there any way to get this to work or an easier alternative to get around this dilemma?

EDIT: changing the third table into 

CREATE TABLE contained (
    bill_id INTEGER REFERENCES bill(bill_id),
    article_id INTEGER NOT NULL,
    revision INTEGER NOT NULL,
    FOREIGN KEY (article_id, revision) REFERENCES article(article_id, revision),
    PRIMARY KEY (bill_id, article_id)
    );

gives me yet another error:

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

which I kind of don't really understand either.

skulpt
  • 527
  • 2
  • 6
  • 25
  • Why can't you use part of the referenced composite FK in your PK? – Tom H Mar 21 '16 at 15:11
  • I'm getting error messages like 'ERROR: column "article_id" named in key does not exist' – skulpt Mar 21 '16 at 15:13
  • 2
    Your `contained` table has only one column: `bill_id` . You should add columns for article_id and revision, too. – joop Mar 21 '16 at 15:14
  • As @joop points out, the column doesn't exist in your table. You can't just declare the foreign keys, you need to actually declare the columns themselves as well. – Tom H Mar 21 '16 at 15:15
  • Ah, I really do feel stupid now. Thanks a lot! – skulpt Mar 21 '16 at 15:16
  • Updated the question since a new issue came up. – skulpt Mar 21 '16 at 15:23
  • 1
    See http://stackoverflow.com/questions/11966420/what-is-causing-error-there-is-no-unique-constraint-matching-given-keys-for-ref/11966490#11966490 – Matteo Tassinari Mar 21 '16 at 15:24
  • Well that certainly does explain it. While the table listed here does have bill_id set as a primary key, it wasn't one in my local database. Thanks! – skulpt Mar 21 '16 at 15:28

0 Answers0