I have 2 tables (innodb).

I need to have table1 have a SET of programs, but i want to pull the programs from table2. table2 has about 25 programs with primary ids.

how would i do this?



(p.s. i know i can do this in my scripts, but i wanted to be able to have cascaded updates if id's change for programs and what not to not have to do it in the script. i'd do it with comma separated ids and then just do a select with multiple or statements.)


[Edited on June 26, 2007 at 11:12 AM. Reason : ]

6/26/2007 11:01:36 AM

example plz

6/26/2007 12:42:41 PM

CREATE TABLE `scenario` (
`id` int(11) NOT NULL auto_increment,
`lock` set('30','45','60') NOT NULL default '30'
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Scenario';




instead of having lock as a hardcoded set here... i'd like to have `lock` in its own table that other tables can pull from so i don't have to change values in every table if i make updates.

but i want to retain the SET feature to allow an insert to have multiple values for lock.

6/26/2007 12:52:06 PM

Use triggers.

6/26/2007 3:09:48 PM

anyone that can show an example?



TO CLARIFY WHAT I'M DOING:

i have a table of 30 different 'products', primary ids.

i want a column in another table to be able to have a SET (1 or more 'products') that link (on update cascade) to the 'products' table in case the 'products' name/descriptions change (to avoid making changes in >1 places).

can someone show an example of that?

[Edited on June 26, 2007 at 4:35 PM. Reason : omar ftw]

6/26/2007 4:20:55 PM

my solution:

create a new table with FK to user_id and FK to programs_id
- for each 'program' a user is interested in, add a row to the new table with their user_id and programs_id

6/26/2007 4:58:38 PM

foreign key with cascading updates, duh.

6/26/2007 6:12:33 PM