From: Christian Mack
Date: March 23 1999 12:16pm
Subject: Re: How to make a joint primary key of two or more fields
List-Archive: http://lists.mysql.com/mysql/832
Message-Id: <36F78628.ECBE3FD6@compal.de>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit

"Martin B. Jespersen" wrote:
> 
> Well there it is, all in the subject.
> 
> I can't figure out how to make two fields function as a single primary key and thus have an unique check on them both but not
> seperately.
> 
> This might be a big RTFM to me, but i haven't been able to find documentation taht i understand about it.. all i found was:
> 
> A PRIMARY KEY can be a multiple-column index. However, you cannot create a multiple-column index using the PRIMARY KEY key
> attibute in a column specification. Doing so will mark only that single column as primary. You must use the PRIMARY
> KEY(index_col_name, ...) syntax.
> 
> it says i have to use PRIMARY KEY(index_col_name, ...), so does the collumns i use for the joint primary have to be indexes?
> 
> lets say i have a table with two int's and one varchar and i want all three to be the primary key together.
> 
> i figured i could just do:
> 
> alter table my_tbl ADD PRIMARY KEY (int_col1,int_col2,varchar_col1);
> 
> but somehow it didn't do the trick for me (i think)... i am out where i have a hard time reaching the bottom, anyone care to get
> me to shore? :-)
> 
> Please bend that syntax in neon for me, and please include an example. :-)
> 
> --
> Martin B. Jespersen

Hi Martin

The above manual snippet only means, that you _can't do_ this:
CREATE TABLE 
	newTable 
	( field1 INT(11)
	, field2 INT(11)
	, field3 VARCHAR(5) PRIMARY KEY (field2, field1, field3)   <====
	, field4 BLOB
	)

You have to use this syntax:
CREATE TABLE
	newTable
	( field1 INT(11)
	, field2 INT(11)
	, field3 VARCHAR(5)
	, field4 BLOB
	, PRIMARY KEY (field2, field1, field3)   <====
	)

In other words:
You have to declare KEY's spreading over multiple columns in a seperate definition line.
This implicitly means, that such a multiple PRIMARY KEY can't use AUTO_INCREMENT.

One Problem is, that each multiple column KEY is only allowed to spread 16 columns.
In your example this isn't a problem (as you only use 3 columns).

The second catch is the maximum overall length of 256 Bytes.
In your example you have 2 integers with 4Bytes = 8 Bytes, and a VARCHAR(x).
If your VARCHAR column is less 256 - 8 = 248 Bytes wide you don't have problems.
But if you have declared it e.g. as VARCHAR(255) you can't declare a KEY over them.

One solution is to use smaller types, e.g. TINYINT, SMALLINT or VARCHAR(200).
The second solution for CHAR and VARCHAR columns is to use only a part of the column for the KEY. Example:
CREATE TABLE
	newTable
	( field1 INT(11)
	, field2 INT(11)
	, field3 VARCHAR(255)
	, field4 BLOB
	, PRIMARY KEY (field2, field1, field3(73) )   <====
	)

This means your column contains the whole info, but the KEY recognises only the first 73 Bytes.

As you want to force uniqueness in these columns, you have to use the first solution.

Hope this helps.

Tschau
Christian