Constraints on Foreign Keys and references

[roylec]

This is the first time I've created my own RELATIONS in the otherwise glorious mySQL, so I need some insight on the following:

I have one table meant to carry a list of possible values, which may grow/shrink over time for example:

  CREATE TABLE CLASS (
     id INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
     PRIMARY KEY (ID),
     group INT NOT NULL UNIQUE,
     );

..and another table in which I would like to introduce a column that is a reference to a row in the previous table:

  CREATE TABLE FOO (
     id INT NOT NULL DEFAULT 0 AUTO_INCREMENT,
     PRIMARY KEY(id),
     class_group INT NOT NULL,
     )

I think you probably get the picture in these simplified table definitions.

What I want to achieve is to make it impossible to insert a row in the FOO table, where the value of class_group is not a valid entry in the CLASS table.

I've read the docs, and lots of examples, but I cant seem to get it to work myself, and I'm just getting myself tied up in knots with what to INDEX, what to REFERENCE as a FOREIGN KEY, what UPDATE and DELETE constraints to apply, wether to TYPE=INNODB or not, etc...

If someone could give me working SQL defs for the above example, this would get me restarted on a project I was enjoying up to now. (or maybe its just Friday)

 

[roylec]

Corrections to the table creations pointed out in an email - I was in a rush when I typed it so...

  CREATE TABLE CLASS (
     ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY,
     group INT NOT NULL UNIQUE
     );

  CREATE TABLE FOO (
     ID INT NOT NULL DEFAULT 0 AUTO_INCREMENT PRIMARY KEY,
     class_group INT NOT NULL
     )