On Sat, 20 Nov 2004 20:16:01 +0300, Mikhail Ramendik <[EMAIL PROTECTED]> wrote: > Hello, > > I gave a quick glance to the 2.0-branch, and found a strange thing. (I'm > looking at the MySQL InnoDB scripts) > > In both 2.0 and cvs 2.0branch, the create scripts for the auto > notifications and auto replies tables are: > > DROP TABLE IF EXISTS dbmail_auto_notifications; > CREATE TABLE dbmail_auto_notifications ( > user_idnr bigint(21) NOT NULL, > notify_address VARCHAR(100) NOT NULL, > INDEX user_idnr_index (user_idnr), > FOREIGN KEY user_idnr_fk (user_idnr) > REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE > ) TYPE=InnoDB; > > DROP TABLE IF EXISTS dbmail_auto_replies; > CREATE TABLE dbmail_auto_replies ( > user_idnr bigint(21) DEFAULT '0' NOT NULL, > reply_body mediumtext, > INDEX user_idnr_index (user_idnr), > FOREIGN KEY user_idnr_fk (user_idnr) > REFERENCES dbmail_users (user_idnr) ON DELETE CASCADE > ) TYPE=InnoDB; > > In 2.0 proper, the update script for same: > > CREATE TABLE IF NOT EXISTS auto_notifications ( > auto_notify_idnr bigint(21) default '0' not null auto_increment, > user_idnr bigint(21) DEFAULT '0' NOT NULL, > notify_address VARCHAR(100), > PRIMARY KEY (auto_notify_idnr), > FOREIGN KEY (`user_idnr`) > REFERENCES `dbmail_users`(`user_idnr`) ON DELETE CASCADE > ) TYPE=InnoDB; > > CREATE TABLE IF NOT EXISTS auto_replies ( > auto_reply_idnr bigint(21) DEFAULT '0' NOT NULL auto_increment, > user_idnr bigint(21) DEFAULT '0' NOT NULL, > reply_body mediumtext, > PRIMARY KEY (auto_reply_idnr), > FOREIGN KEY (`user_idnr`) > REFERENCES `dbmail_users`(`user_idnr`) ON DELETE CASCADE > ) TYPE=InnoDB; > > 2.0branch adds a key on user_idnr to the auto_notifications table. KEY, > not INDEX as in the create scripts. > > So there was, and remains, a difference in the key/index structure for > these tables, depending on whether they were created on a new install or > on an update. Is this OK? > > Yours, Mikhail Ramendik >
Actually, things are not as wrong as you think they are. In the update script, the tables are first created in the same way as they are in dbmail 1.2.x. In dbmail 1.2.x, the tables are not always present, so they're created that way if they are not. After that, from line 182, the tables are changed to the dbmail 2.0 schema. However, In the create tables script, user_idnr is an INDEX, and in the update script, it's a PRIMARY KEY. To be honest, I don't know what to here exactly. user_idnr is a Foreign Key to dbmail_users.user_idnr. Is it bad practise to make it Primary Key in the table? Any database expert that knows? I was going to look it up in my databases book, but that's at the office, and I'm at home now. Ilja
