From: Martijn Tonies
Date: April 3 2006 1:28pm
Subject: Re: Syntax Question Constraint, Index
List-Archive: http://lists.mysql.com/mysql/196455
Message-Id: <05a401c65722$8b887bc0$cd02a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit

>Create table events (
>uid BIGINT NOT NULL AUTO_INCREMENT,
>name VARCHAR(255),
>start_date DATE,
>duration INTEGER,
>location_id BIGINT,
>primary key (uid)
>);
>
>Create table locations (
>uid BIGINT NOT NULL AUTO_INCREMENT,
>name VARCHAR(255),
>address VARCHAR(255),
>primary key (uid)
>)
>
>Alter table events add index (location_id), add
>Constraint FKB307E11920EBB9E5 foreign key (location_id) references
>locations(uid)
>// Here is my conclusion, and I was hoping someone may back this up.
>Events has  a primary key of UID that is auto_incremeneted.
>Locations has a primary key of UID that is also incremented.
>
>The constraint and index are where I have questions. What is the index
>and constraint doing? I can't seem to get my mind around what that alter
>statement is trying to accomplish.

Well, the index part is adding, guess what, an index for column
"location_id", and the constraint part is adding a referential
constraint. That is, values in column events.location_id need
to exist in table "locations.uid".

The referential constraint is only enforced for InnoDB tables.

Martijn Tonies
Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle &
MS SQL Server
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com