fix foreign keys in add_header_tables.mysql

git-svn-id: https://svn.ic-s.nl/svn/dbmail/trunk/dbmail@1706 7b491191-dbf0-0310-aff6-d879d4d69008

1 files changed, 44 insertions, 31 deletions

diff --git a/sql/mysql/add_header_tables.mysql b/sql/mysql/add_header_tables.mysql
index 1fddcd69..2e08777c 100644
--- a/sql/mysql/add_header_tables.mysql
+++ b/sql/mysql/add_header_tables.mysql
@@ -13,6 +13,8 @@
 # store all headers by storing all headernames and headervalues in separate
 # tables.
 #
+SET FOREIGN_KEY_CHECKS=0;
+START TRANSACTION;
 
 DROP TABLE IF EXISTS dbmail_headername;
 CREATE TABLE dbmail_headername (
@@ -24,16 +26,20 @@ CREATE TABLE dbmail_headername (
 
 DROP TABLE IF EXISTS dbmail_headervalue;
 CREATE TABLE dbmail_headervalue (
-	headername_id	BIGINT NOT NULL
-		REFERENCES dbmail_headername(id)
-		ON UPDATE CASCADE ON DELETE CASCADE,
-        physmessage_id	BIGINT NOT NULL
-		REFERENCES dbmail_physmessage(id)
-		ON UPDATE CASCADE ON DELETE CASCADE,
+	headername_id	BIGINT NOT NULL,
+        physmessage_id	BIGINT NOT NULL,
 	id		BIGINT NOT NULL AUTO_INCREMENT,
 	headervalue	VARCHAR(255) NOT NULL DEFAULT '',
 	PRIMARY KEY (id),
-	UNIQUE (physmessage_id, id)
+	UNIQUE (physmessage_id, id),
+	UNIQUE (physmessage_id, headername_id, headervalue),
+	INDEX (headername_id),
+	FOREIGN KEY (headername_id)
+		REFERENCES dbmail_headername(id) ON UPDATE CASCADE ON DELETE CASCADE,
+	FOREIGN KEY (physmessage_id)
+		REFERENCES dbmail_physmessage(id) ON UPDATE CASCADE ON DELETE CASCADE
+	
+		
 ) type=InnoDB ;
 
 # provide separate storage of commonly used headers
@@ -47,24 +53,26 @@ CREATE TABLE dbmail_headervalue (
 
 DROP TABLE IF EXISTS dbmail_inreplytofield;
 CREATE TABLE dbmail_inreplytofield (
-        physmessage_id  BIGINT NOT NULL
-			REFERENCES dbmail_physmessage(id)
-			ON UPDATE CASCADE ON DELETE CASCADE,
+        physmessage_id  BIGINT NOT NULL,
 	id		BIGINT NOT NULL AUTO_INCREMENT,
 	inreplytofield	VARCHAR(100) NOT NULL DEFAULT '',
 	PRIMARY KEY (id),
-	UNIQUE (physmessage_id, id)
+	UNIQUE (physmessage_id, id),
+	FOREIGN KEY (physmessage_id)
+			REFERENCES dbmail_physmessage(id)
+			ON UPDATE CASCADE ON DELETE CASCADE
 ) type=InnoDB ;
 
 DROP TABLE IF EXISTS dbmail_referencesfield;
 CREATE TABLE dbmail_referencesfield (
-        physmessage_id  BIGINT NOT NULL
-			REFERENCES dbmail_physmessage(id)
-			ON UPDATE CASCADE ON DELETE CASCADE,
+        physmessage_id  BIGINT NOT NULL,
 	id		BIGINT NOT NULL AUTO_INCREMENT,
 	referencesfield	VARCHAR(100) NOT NULL DEFAULT '',
 	PRIMARY KEY (id),
-	UNIQUE (physmessage_id, id)
+	UNIQUE (physmessage_id, id),
+	FOREIGN KEY (physmessage_id)
+			REFERENCES dbmail_physmessage(id)
+			ON UPDATE CASCADE ON DELETE CASCADE
 ) type=InnoDB ;
 
 
@@ -77,47 +85,51 @@ CREATE TABLE dbmail_referencesfield (
 
 DROP TABLE IF EXISTS dbmail_fromfield;
 CREATE TABLE dbmail_fromfield (
-        physmessage_id  BIGINT NOT NULL
-			REFERENCES dbmail_physmessage(id)
-			ON UPDATE CASCADE ON DELETE CASCADE,
+        physmessage_id  BIGINT NOT NULL,
 	id		BIGINT NOT NULL AUTO_INCREMENT,
 	fromname	VARCHAR(100) NOT NULL DEFAULT '',
 	fromaddr	VARCHAR(100) NOT NULL DEFAULT '',
 	PRIMARY KEY (id),
-	UNIQUE (physmessage_id, id)
+	UNIQUE (physmessage_id, id),
+	FOREIGN KEY (physmessage_id)
+			REFERENCES dbmail_physmessage(id)
+			ON UPDATE CASCADE ON DELETE CASCADE
 ) type=InnoDB ;
 DROP TABLE IF EXISTS dbmail_tofield;
 CREATE TABLE dbmail_tofield (
-        physmessage_id  BIGINT NOT NULL
-			REFERENCES dbmail_physmessage(id)
-			ON UPDATE CASCADE ON DELETE CASCADE,
+        physmessage_id  BIGINT NOT NULL,
 	id		BIGINT NOT NULL AUTO_INCREMENT,
 	toname		VARCHAR(100) NOT NULL DEFAULT '',
 	toaddr		VARCHAR(100) NOT NULL DEFAULT '',
 	PRIMARY KEY (id),
-	UNIQUE (physmessage_id, id)
+	UNIQUE (physmessage_id, id),
+	FOREIGN KEY (physmessage_id)
+			REFERENCES dbmail_physmessage(id)
+			ON UPDATE CASCADE ON DELETE CASCADE
 ) type=InnoDB ;
 DROP TABLE IF EXISTS dbmail_replytofield;
 CREATE TABLE dbmail_replytofield (
-        physmessage_id  BIGINT NOT NULL
-			REFERENCES dbmail_physmessage(id)
-			ON UPDATE CASCADE ON DELETE CASCADE,
+        physmessage_id  BIGINT NOT NULL,
 	id		BIGINT NOT NULL AUTO_INCREMENT,
 	replytoname	VARCHAR(100) NOT NULL DEFAULT '',
 	replytoaddr	VARCHAR(100) NOT NULL DEFAULT '',
 	PRIMARY KEY (id),
-	UNIQUE (physmessage_id, id)
+	UNIQUE (physmessage_id, id),
+	FOREIGN KEY (physmessage_id)
+			REFERENCES dbmail_physmessage(id)
+			ON UPDATE CASCADE ON DELETE CASCADE
 ) type=InnoDB ;
 DROP TABLE IF EXISTS dbmail_ccfield;
 CREATE TABLE dbmail_ccfield (
-        physmessage_id  BIGINT NOT NULL
-			REFERENCES dbmail_physmessage(id)
-			ON UPDATE CASCADE ON DELETE CASCADE,
+        physmessage_id  BIGINT NOT NULL,
 	id		BIGINT NOT NULL AUTO_INCREMENT,
 	ccname		VARCHAR(100) NOT NULL DEFAULT '',
 	ccaddr		VARCHAR(100) NOT NULL DEFAULT '',
 	PRIMARY KEY (id),
-	UNIQUE (physmessage_id, id)
+	UNIQUE (physmessage_id, id),
+	FOREIGN KEY (physmessage_id)
+			REFERENCES dbmail_physmessage(id)
+			ON UPDATE CASCADE ON DELETE CASCADE
 ) type=InnoDB ;
 
 # Some other fields will also be commonly used for search/sort but do not warrant 
@@ -137,3 +149,4 @@ CREATE TABLE dbmail_ccfield (
 
 COMMIT;
 
+SET FOREIGN_KEY_CHECKS=0;