From: Martijn Tonies
Date: October 6 2006 7:32am
Subject: Re: multiple primary keys on one table?
List-Archive: http://lists.mysql.com/mysql/202361
Message-Id: <00d001c6e919$8b18cb70$cd02a8c0@martijnws>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="iso-8859-1"
Content-Transfer-Encoding: 7bit



> I have a primary key set on a table which consists of the combination of
the
> values: firstname, lastname, and a schedule_id (BIGINT(20))... I have this
> so the records in this table do not have duplicates, being that no one
> record should have the exact same name and schedule_id identifier.
>
> However, I want to keep this same restriction while also ensuring that no
> two records have the same email_address and schedule_id identifier...
>
> You can't have the db enforce two different primary keys on one table, so
> how would I implement having this kind of restriction, which, in itself,
> seems to require that I have a second primary key to enforce another
> constraint to dissalow records to be added that carry the same combination
> of: email_address and schedule_id?

You cannot have multiple PRIMARY key constraints, that's why it's
called "primary".

You can, however, use multiple "unique constraints", which do (almost)
the same.

With regard to other replies: indices are used for quick data retrieval,
constraints for business requirements. That there happens to be something
as a "unique index" is an implementation artifact.



Martijn Tonies
Database Workbench - development tool for MySQL, and more!
Upscene Productions
http://www.upscene.com
My thoughts:
http://blog.upscene.com/martijn/
Database development questions? Check the forum!
http://www.databasedevelopmentforum.com