Fix up foreign key from videos to announcements

All videos should be attached to an announcement from the same bucket,
so make the schema enforce that. One could probably argue that this means
that bucket_slug doesn't need to exist in the videos table but in my
opinion one would be dead wrong :) - the bucket is an integral part of the
video so while it's sort of duplication, it isn't denormalisation.

diff --git a/share/sql/idiotbox-1.0-sqlite.sql b/share/sql/idiotbox-1.0-sqlite.sql
index c9d24ae..d2f0d7a 100644 (file)
--- a/share/sql/idiotbox-1.0-sqlite.sql
+++ b/share/sql/idiotbox-1.0-sqlite.sql
@@ -15,6 +15,8 @@ CREATE TABLE videos (
   name TEXT NOT NULL,
   author TEXT NOT NULL,
   details TEXT NOT NULL DEFAULT '',
-  announcement_id INTEGER REFERENCES announcements(id),
-  PRIMARY KEY (slug, bucket_slug)
+  announcement_id INTEGER NOT NULL,
+  PRIMARY KEY (slug, bucket_slug),
+  FOREIGN KEY (announcement_id, bucket_slug)
+    REFERENCES announcements(id, bucket_slug)
 );