primary key and unique

Hi

Hope someone would be generous to explainme the following

1.
Difference between a primary key and Unique in Mysql. Why there are table where there is a field which has been defined as primary key contain another field defeined as unique key.

2.
Index key ? are they same with primary key ..

3. I did a sample table with syntax:

CREATE TABLE `keys` (
`id` INT( 3 ) NOT NULL AUTO_INCREMENT,
`matric` VARCHAR( 20 ) NOT NULL ,
PRIMARY KEY ( `id` ) ,
UNIQUE (
`matric`
)
);


The table is succesfully created but when i say 'describe keys' the mysql returns an error :

mysql> describe keys;
ERROR 1064: You have an error in your SQL syntax near 'keys' at line 1


Thanks,

Rathaur

Index:

An index is a structure in a table that orders the data. It allows the database to access data quickly (In MySQL its implemented using B-tree algorithms).

Primary Key:

This is an index that cannot be NULL, Primary Keys are used in building relationships between tables in a database. (an index is automatically created on the primary key). The difference between primary and ordinary keys is that there can be multiple keys, but only one primary key.

Unique:
Unique and Index are same, the difference is, in Unique, duplicate are not allowed in any circumstances and that is enforced by database server.

Thanks

Thanks, I am confused again, pls do clear me. You said there is only 1 primary key in a table, but I do have a table with 2 primary keys.

I created the table with 1 primary key, but later I alter the table to have 2 primary key

look at this 2 primary keys are here


mysql> describe k2;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(3) | | PRI | 0 | |
| name | varchar(33) | | | | |
| name2 | varchar(30) | | | | |
| bins | varchar(30) | | PRI | | |
| trial | varchar(5) | | | | |
+-------+-------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

mysql>

Your table with 2 primary keys means that the 2 fields together form one unique key. Each field by itself may have repeating values, but both keys put together must be unique. Take a look:



You see, there are two Bobs and two Smiths, but each record has a unique key.

Is it a composite primary key????