CODEWITHSUNDEEP
databasepostgresqlforeign-keysconstraintsdatabase-schema
Jerry
Jerry

Reputation: 2567

(PostgreSQL) "Advanced" Check Constraint Question

I use PostgreSQL but am looking for SQL answer as standard as possible.

I have the following table "docs" --

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 version    | integer                | not null default 1
 link_id    | character varying(32)  | 
 content    | character varying(128) | 
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id, version)

id and link_id are for documents that have linkage relationship between each other, so link_id self references id.

The problem comes with version. Now id is no longer the primary key (won't be unique either) and can't be referenced by by link_id as foreign key --

my_db=# ALTER TABLE docs ADD FOREIGN KEY(link_id) REFERENCES docs (id) ; 
ERROR:  there is no unique constraint matching given keys for referenced table "docs"

I tried to search for check constraint on something like "if exists" but didn't find anything.

Any tip will be much appreciated.

Upvotes: 0

Views: 1308

Answers (3)

user533832
user533832

Reputation:

Sticking as close to your model as possible, you can split your table into two, one which has 1 row per 'doc' and one with 1 row per 'version':

You have the following table "versions" --

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 version    | integer                | not null default 1
 content    | character varying(128) | 
Indexes:
    "versions_pkey" PRIMARY KEY, btree (id, version)

And the following table "docs" --

    Column  |          Type          | Modifiers              
------------+------------------------+--------------------
 id         | character varying(32)  | not null
 link_id    | character varying(32)  | 
Indexes:
    "docs_pkey" PRIMARY KEY, btree (id)

now 

my_db=# ALTER TABLE docs ADD FOREIGN KEY(link_id) REFERENCES docs (id) ;

is allowed, and you also want:

my_db=# ALTER TABLE versions ADD FOREIGN KEY(id) REFERENCES docs;

of course there is nothing stoping you getting a 'combined' view similar to your original table:

CREATE VIEW v_docs AS
SELECT id, version, link_id, content from docs join versions using(id);

Upvotes: 1

Magnus Hagander
Magnus Hagander

Reputation: 25088

Depending on if it's what you want, you can simply create a FOREIGN KEY that includes the version field. That's the only way to point to a unique row...

If that doesn't work, you can write a TRIGGER (for all UPDATEs and INSERTs on the table) that makes the check. Note that you will also need a trigger on the docs table, that restricts modifications on that table that would break the key (such as a DELETE or UPDATE on the key value itself).

You cannot do this with a CHECK constraint, because a CHECK constraint cannot access data in another table.

Upvotes: 0

jishi
jishi

Reputation: 24614

I usually do like this:

table document (id, common, columns, current_revision)
table revision (id, doc_id, content, version)

which means that document has a one-to-many relation with it's revisions, AND a one-to-one to the current revision.

That way, you can always select a complete document for the current revision with a simple join, and you will only have one unique row in your documents table which you can link parent/child relations in, but still have versioning.

Upvotes: 4

Related Questions