Changeset 368:c9494920e8fa

Timestamp:

05/02/2009 07:21:24 PM (3 years ago)

Author:

Dmitry Nezhevenko <dion@…>

Branch:

default

Message:

Add foreign keys for postgresql

Files:

• sql/pgsql.sql (modified) (4 diffs)

sql/pgsql.sql

@@ -16 +16 @@
 );
 
+DROP TABLE IF EXISTS conferences CASCADE;
+CREATE TABLE conferences (
+  name varchar(50) NOT NULL,
+  id SERIAL,
+  nick varchar(50) NOT NULL,
+  created timestamp NOT NULL,
+  autojoin boolean NOT NULL default true,
+  online boolean NOT NULL default false,
+  joined timestamp default NULL,
+  autoleave boolean NOT NULL default true,
+  owner varchar(200) NULL,
+  PRIMARY KEY (name),
+  UNIQUE (id)
+);
+
 DROP TABLE IF EXISTS conference_alists CASCADE;
 CREATE TABLE conference_alists (
@@ -29 +44 @@
   expire timestamp default NULL,
   PRIMARY KEY (id),
+  FOREIGN KEY(conference_id) REFERENCES conferences(id) ON DELETE CASCADE,
   UNIQUE (conference_id,list,matcher,test,value, child_id)
 );
@@ -40 +56 @@
   temporary boolean NOT NULL default false,
   created timestamp default NULL,
-  PRIMARY KEY (id)
+  PRIMARY KEY (id),
+  FOREIGN KEY(conference_id) REFERENCES conferences(id) ON DELETE CASCADE
 );
 
@@ -53 +70 @@
   joined timestamp NOT NULL,
   lastaction timestamp NOT NULL,
-  PRIMARY KEY (id)
-);
-
-DROP TABLE IF EXISTS conferences CASCADE;
-CREATE TABLE conferences (
-  name varchar(50) NOT NULL,
-  id SERIAL,
-  nick varchar(50) NOT NULL,
-  created timestamp NOT NULL,
-  autojoin boolean NOT NULL default true,
-  online boolean NOT NULL default false,
-  joined timestamp default NULL,
-  autoleave boolean NOT NULL default true,
-  owner varchar(200) NULL,
-  PRIMARY KEY (name),
-  UNIQUE (id)
+  PRIMARY KEY (id),
+  FOREIGN KEY(conference_id) REFERENCES conferences(id) ON DELETE CASCADE,
+  FOREIGN KEY(jid) REFERENCES conference_jids(id) ON DELETE CASCADE
 );