List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:April 20 2002 9:55pm
Subject:Re: missing foreign keys
View as plain text  
At 18:22 +0200 4/20/02, Robert Douglass wrote:
>Hello,
>I'm trying to apply my limited SQL knowledge to my newly acquired MySql
>database and am running into the following problem:
>In the (very good) manual on the mysql website, this example is given
>CREATE TABLE persons (
>     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>     name CHAR(60) NOT NULL,
>     PRIMARY KEY (id)
>);
>
>CREATE TABLE shirts (
>     id SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT,
>     style ENUM('t-shirt', 'polo', 'dress') NOT NULL,
>     color ENUM('red', 'blue', 'orange', 'white', 'black') NOT NULL,
>     owner SMALLINT UNSIGNED NOT NULL REFERENCES persons,
>     PRIMARY KEY (id)
>);
>
>INSERT INTO persons VALUES (NULL, 'Antonio Paz');
>
>INSERT INTO shirts VALUES
>(NULL, 'polo', 'blue', LAST_INSERT_ID()),
>(NULL, 'dress', 'white', LAST_INSERT_ID()),
>(NULL, 't-shirt', 'blue', LAST_INSERT_ID());
>
>How do you do the same thing later though, when LAST_INSERT_ID() won't work
>any more? How can I insert into shirts later, when 'Antonio Paz' isn't the
>last thing inserted? In my prior SQL experience, I'd do something like this:
>CONSTRAINT personWithShirts FOREIGN KEY () REFERENCES Person ()).  How in
>MySql?

Look up the key later when you need it.

You're saying that adding a constraint to shirts magically allows the
database to figure out which id from the persons table to associate with
new shirts records?

>
>Thanks,
>RD

Thread
missing foreign keysRobert Douglass20 Apr
  • Re: missing foreign keysPaul DuBois20 Apr