Indexes and integrity 1

[Echilon]

I'm trying to get an index working on a table. This is my data structure (with non important columns omitted):

Each translation in the translations table can be used by none or more elements.

For example:
--Elements--
(id, translationid, elementname)
1000, 1, 'btnDay'
1001, 1, 'itmDay'
1001, 1, 'GeneralDay'
1002, 2, 'btnMonth'
1003, 2, 'itmMonth'
1004, 2, 'GeneralMonth'

--Translations--
(id, translationid, language, translation)
9000, 1, 'en-GB', 'Day'
9001, 1, 'de-DE', 'Tag'
9002, 1, 'fr-FR', 'Jour'
9003, 2, 'en-GB', 'Month'
9004, 2, 'de-DE', 'Monat'
9005, 2, 'fr-FR', 'Mois'

I can insert into the translation table but I can't delete anything.

What I need to happen is: when elements.`translationid` is changed, all matching rows in the translations table should be updated to reflect the new ID to use, but when an element is deleted, the translations should not be deleted.

I also need to be able to delete records from the translations table without affecting the elements table. I know this could mean I end up with rows in the elements table which have no matching translationid. Is it possible to set elements.`translationid` to null ONLY if there are no more matching rows in translations?

This is the table structure, if it helps.

CREATE TABLE `elements` (
`id` int(11) NOT NULL auto_increment,
`projectid` varchar(255) NOT NULL,
`elementname` varchar(255) NOT NULL,
`translationid` int(10) default NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uniqueelement` (`projectid`,`elementname`),
KEY `fk_translationid` (`translationid`),
CONSTRAINT `fk_translationid` FOREIGN KEY (`translationid`) REFERENCES `translations` (`translationid`) ON DELETE NO ACTION ON UPDATE CASCADE
)

CREATE TABLE `translations` (
`translationid` int(10) NOT NULL default '0',
`language` varchar(10) NOT NULL,
`translation` text NOT NULL,
PRIMARY KEY (`translationid`,`language`)
)

 

[r937]

What I need to happen is: when elements.`translationid` is changed, all matching rows in the translations table should be updated to reflect the new ID to use

that won't be possible without a trigger or something, because the foreign key ON UPDATE CASCADE works in the other direction


when translations.`translationid` changes, the ON UPDATE CASCADE will propagate the change down to elements.`translationid`

by the way, you should be posting in forum436, not here



r937.com | rudy.ca

 

[Echilon]

That's what I was hoping wouldn't happen. This might be easier to do programatically instead.