MySQL ODBC Connector - Syntax Question Constraint, Index

Author

Syntax Question Constraint, Index

Scott Purcell

2006-04-03, 9:29 am

------ _=_NextPart_001_01C6
571F.FA6BC038
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable

I ran into some syntax over the weekend, that I am trying to make sense
of. Here is the create table statements.

=20

Drop table if exists events

Drop table if exists locations

=20

Create table events (

uid BIGINT NOT NULL AUTO_INCREMENT,

name VARCHAR(255),

start_date DATE,

duration INTEGER,

location_id BIGINT,

primary key (uid)

);

=20

Create table locations (

uid BIGINT NOT NULL AUTO_INCREMENT,

name VARCHAR(255),

address VARCHAR(255),

primary key (uid)

)

=20

Alter table events add index (location_id), add

Constraint FKB307E11920EBB9E5 foreign key (location_id) references
locations(uid)

=20

=20

// 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.

=20

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.

=20

Thanks,

=20

=20

------ _=_NextPart_001_01C6
571F.FA6BC038--

Martijn Tonies

2006-04-03, 9:29 am

>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. databasedevelopmentf
orum.com

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw

Sponsored Links