Re: Normalization w/ MySQL?

[Bob Kline]

On Tue, 5 Oct 1999, Steve Freitas wrote:

> Someone please correct me on this if I'm wrong.
> 
> The way I understand MySQL is that foreign keys don't work. What this 
> means is that MySQL is incompatible with any database schema that's in 
> normalized form. Is this true?
> 
> And if this is true, what does everybody do? If I want to, say, add 
> something to the shopping cart table for someone, I'd have to insert 
> their account number along with everything else. And if they changed 
> their account number over in their account management screen (unlikely 
> but go with me), unless my program went through and modified the stored 
> account number in the shopping cart table, it'd be lost to them, wouldn't 
> it?
> 

I think you may have a misperception of what foreign keys do.  You are
correct that the functionality is not yet implemented in MySQL (though
the syntax is accepted for compatibility with standard SQL).  When
foreign key constraint support is implemented, it blocks modification of
data which would result in a row whose foreign key does not have a match
with the primary key of the table which it references.  For example:

1> CREATE TABLE state(code CHAR(2) PRIMARY KEY, name VARCHAR(20))
2> go
1> CREATE TABLE city(state CHAR(2) REFERENCES state, name VARCHAR(40))
2> go
1> INSERT INTO state VALUES('VA', 'Virginia')
2> go
(1 row affected)
1> INSERT INTO city VALUES('VA', 'Richmond')
2> go
(1 row affected)
1> INSERT INTO city VALUES('AV', 'Falls Church')
2> go
Msg 546, Level 16, State 1:
Line 1:
Foreign key constraint violation occurred, dbname =  'pubs2', table name
=
'city', constraint name = 'city_state_896006223'.
Command has been aborted.

What foreign keys do *not* do is ripple changes you make to a primary
key to modify the foreign keys referring to the changed primary key
automatically.  You must do this yourself, whether your DBMS implements
foreign key functionality or not.

-- 
Bob Kline
mailto:bkline@stripped
http://www.rksystems.com