[Resolved] creating REFER commands at table creation

[sfullman]

Hi There,

Running mysql 4.0.15-standard. I know how to create relational references on a mysql table using phpMyAdmin (very educational), but how would you create a table with the references included AT THE TIME OF TABLE CREATION?

A simple example and I'd probably pick it up.

gracias,
Sam

[rachel2004]

If I understood you correctly, you're looking for something like the following:

CREATE TABLE Users (
userID int not null auto_increment,
primary key (myTableID),
username varchar (20)
);

CREATE TABLE Emails (
emailID int not null auto_increment,
primary key (emailID),
email varchar (255),
userID int not null
);

SELECT Users.username,
Emails.email
FROM Users, Emails
WHERE Users.userID = Emails.userID;

[sfullman]

Actually no.

I want to set InnoDB relationships AT THE TIME THE TABLE IS CREATED. An example is:

(Modules_ID) REFER relatebase/relatebase_modules(ID) ON UPDATE CASCADE ON DELETE CASCADE

But I want to do it at the time the table is created.

Sam

[dopey]

From http://dev.mysql.com/doc/mysql/en/InnoDB_foreign_key_constraints.html

CREATE TABLE parent(id INT NOT NULL,
                    PRIMARY KEY (id)
) TYPE=INNODB;

CREATE TABLE child(id INT, parent_id INT,
                   INDEX par_ind (parent_id),
                   FOREIGN KEY (parent_id) REFERENCES parent(id)
                     ON DELETE CASCADE
) TYPE=INNODB;

[sfullman]

Spot on, thx for the help