From 018bdbf096cc7de6795413568e1ce1b56490aa6a Mon Sep 17 00:00:00 2001
From: Matthias Andreas Benkard <code@mail.matthias.benkard.de>
Date: Wed, 2 Mar 2011 18:00:32 +0100
Subject: Database schema: Add missing primary key constraints.

---
 schema.sql | 9 +++++++--
 1 file changed, 7 insertions(+), 2 deletions(-)

(limited to 'schema.sql')

diff --git a/schema.sql b/schema.sql
index 63d227f..788228d 100644
--- a/schema.sql
+++ b/schema.sql
@@ -25,18 +25,21 @@ CREATE TABLE users(
 CREATE TABLE passwords(
   "user"   INTEGER NOT NULL,
   password VARCHAR NOT NULL,
+  PRIMARY KEY ("user", password),
   FOREIGN KEY ("user") REFERENCES users
 );  
 
 CREATE TABLE openids(
   "user" INTEGER NOT NULL,
   openid VARCHAR NOT NULL,
+  PRIMARY KEY ("user", openid),
   FOREIGN KEY ("user") REFERENCES users
 );  
 
 CREATE TABLE login_certificates(
   "user"      INTEGER NOT NULL,
-  certificate BYTEA NOT NULL,
+  certificate BYTEA  NOT NULL,
+  PRIMARY KEY ("user", certificate),
   FOREIGN KEY ("user") REFERENCES users
 );
 
@@ -90,12 +93,14 @@ CREATE TABLE article_revision_characteristics(
   revision       INTEGER NOT NULL,
   characteristic VARCHAR NOT NULL,
   value          VARCHAR,
-  FOREIGN KEY (revision) REFERENCES article_revisions
+  FOREIGN KEY (revision) REFERENCES article_revisions,
+  UNIQUE (revision, characteristic, value)
 );
 
 CREATE TABLE article_revision_parenthood(
   parent_id INTEGER NOT NULL,
   child_id  INTEGER NOT NULL,
+  PRIMARY KEY (parent_id, child_id),
   FOREIGN KEY (parent_id) REFERENCES article_revisions,
   FOREIGN KEY (child_id)  REFERENCES article_revisions
 );
-- 
cgit v1.2.3

