List:General Discussion« Previous MessageNext Message »
From:Martijn Tonies Date:April 3 2006 1:28pm
Subject:Re: Syntax Question Constraint, Index
View as plain text  
>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

Thread
Syntax Question Constraint, IndexScott Purcell3 Apr
  • Re: Syntax Question Constraint, IndexMartijn Tonies3 Apr