Hum right, Now it is correct. CREATE TABLE `dbmail_subscription` ( `user_id` bigint(21) NOT NULL, `mailbox_id` bigint(21) NOT NULL, PRIMARY KEY (`user_id`,`mailbox_id`), KEY `user_id_index` (`user_id`), KEY `mailbox_id_index` (`mailbox_id`), CONSTRAINT `dbmail_subscription_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `dbmail_subscription_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8
But, I was checking and I have a lot of records without a mailbox, the select I did was: --- select *,(select count(*) from dbmail_mailboxes where mailbox_idnr=mailbox_id) as tot from dbmail_subscription having tot=0; --- And I have 286 records, that has no existing connection to dbmail_mailboxes. Can I safely delete this records from dbmail_subscriptions? And for dbmail_mailboxes: --- select *,(select count(*) from dbmail_subscription where mailbox_id=mailbox_idnr) as tot from dbmail_mailboxes having tot=0; --- I have 354 records that have no match in dbmail_subscription. My sql statements are correct, right? What can I do with this? -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Paul J Stevens Sent: domingo, 10 de Fevereiro de 2008 16:58 To: DBMail mailinglist Subject: Re: [Dbmail] DBMail 2.3.2 released I see three subscription tables. -- -- Table structure for table `dbmail_subscription` -- DROP TABLE IF EXISTS `dbmail_subscription`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `dbmail_subscription` ( `user_id` bigint(21) NOT NULL, `mailbox_id` bigint(21) NOT NULL, PRIMARY KEY (`user_id`,`mailbox_id`), KEY `user_id_index` (`user_id`), KEY `mailbox_id_index` (`mailbox_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; SET character_set_client = @saved_cs_client; This one doesn't have *any* foreign keys. Broken! -- -- Table structure for table `dbmail_subscription_10022008` -- DROP TABLE IF EXISTS `dbmail_subscription_10022008`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `dbmail_subscription_10022008` ( `user_id` bigint(21) NOT NULL, `mailbox_id` bigint(21) NOT NULL, PRIMARY KEY (`user_id`,`mailbox_id`), KEY `user_id_index` (`user_id`), KEY `mailbox_id_index` (`mailbox_id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; SET character_set_client = @saved_cs_client; Looks like you can drop that one all together. Your call. -- -- Table structure for table -- DROP TABLE IF EXISTS `dbmail_subscription_com_reg_apagados`; SET @saved_cs_client = @@character_set_client; SET character_set_client = utf8; CREATE TABLE `dbmail_subscription_com_reg_apagados` ( `user_id` bigint(21) NOT NULL, `mailbox_id` bigint(21) NOT NULL, PRIMARY KEY (`user_id`,`mailbox_id`), KEY `user_id_index` (`user_id`), KEY `mailbox_id_index` (`mailbox_id`), CONSTRAINT `dbmail_subscription_com_reg_apagados_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `dbmail_users` (`user_idnr`) ON DELETE CASCADE ON UPDATE CASCADE, CONSTRAINT `dbmail_subscription_com_reg_apagados_ibfk_2` FOREIGN KEY (`mailbox_id`) REFERENCES `dbmail_mailboxes` (`mailbox_idnr`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=utf8; SET character_set_client = @saved_cs_client; This one is clean! Insert the data from the first into this one, ignore all errors, drop the first, rename the third to the last, y ya esta. So: #> mysqldump -t dbmail dbmail_subscription > subscription_data.txt #> mysql dbmail mysql> drop table dbmail_subscription; mysql> alter table dbmail_subscription_com_reg_apagados rename dbmail_subscription; mysql> exit; #> mysql -f dbmail < subscription_data.txt done. -- ________________________________________________________________ Paul Stevens paul at nfg.nl NET FACILITIES GROUP GPG/PGP: 1024D/11F8CD31 The Netherlands________________________________http://www.nfg.nl _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail _______________________________________________ DBmail mailing list [email protected] https://mailman.fastxs.nl/mailman/listinfo/dbmail
