×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Indexes and integrity

Indexes and integrity

Indexes and integrity

(OP)
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`)
)

RE: Indexes and integrity

Quote:

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: MySQL AB: MySQL, not here

smile

r937.com | rudy.ca

RE: Indexes and integrity

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close