Difference between UNIQUE and PRIMARY

[chesneil]

Hi all,
Stupid question, but... what's the difference between Unique and Primary? I'm referring to the phpmyAdmin interface. I thought the primary field was unique.

Thanks
c

[Kiwi]

PRIMARY refers to a field or a set of fields used to identify each row in a table. UNIQUE refers to a field that cannot be repeated in a table. The primary key is, by necessity, a unique identifier, but any field may be defined as unique. The primary key can be a composite, consisting of more than one field. Each field within a composite key can be repeated, but each combination of fields must be unique.

[chesneil]

Originally Posted by Kiwi

PRIMARY refers to a field or a set of fields used to identify each row in a table. UNIQUE refers to a field that cannot be repeated in a table. The primary key is, by necessity, a unique identifier, but any field may be defined as unique. The primary key can be a composite, consisting of more than one field. Each field within a composite key can be repeated, but each combination of fields must be unique.

Thanks for the explanation, Kiwi. When you say "...a field that cannot be repeated in a table" you mean that entries in that column cannot be repeated. Not that the column names can't be repeated (which, of course, they can't).


OK. Next basic question. Why do we need a 'Primary' field. I understand the need for a field that uniquely identifies each row, i.e. an 'ID' field, but why declare it as 'Primary'. What does it mean?

In anticipation...

[Brandoe85]

You're right a primary key is something that uniquely identifies a row, and I think one major reason you declare it as primary is that in mysql primary keys are automatically indexed for you.

[chesneil]

Originally Posted by Brandoe85

You're right a primary key is something that uniquely identifies a row, and I think one major reason you declare it as primary is that in mysql primary keys are automatically indexed for you.

And 'indexing' is what exactly?

[Brandoe85]

Indexes allow for quick searches. Heres an article that explains indexing.

[Kiwi]

The major reason for declaring a primary key is that it is necessary to have a mechanism for uniquely identifying each record in your table. Indexing is part of the mechanisms used to ensure you have the unique identifier.

It's important to remember that a primary key (or an index, for that matter) does not have to be based on a single field. Also, building an index does take up space: every index you create takes up roughly the same space as a table consisting of the indexed fields. Creating indexes on char and text fields is not really a good idea; they work best on fixed length number fields.

And, yes, I did mean that a value in a field specified as unique cannot be repeated.

Some sample code for creating a composite primary key is:

PHP Code:

CREATE TABLE invoice_ln (
 invoice_number INTEGER(4) UNSIGNED ZEROFILL NOT NULL,
 invoice_line_number INTEGER(2) UNSIGNED ZEROFILL NOT NULL,
 descr TEXT,
 PRIMARY KEY (invoice_number, invoice_line_number)
); 


[Trilok]

Originally Posted by Kiwi

The major reason for declaring a primary key is that it is necessary to have a mechanism for uniquely identifying each record in your table. Indexing is part of the mechanisms used to ensure you have the unique identifier.

It's important to remember that a primary key (or an index, for that matter) does not have to be based on a single field. Also, building an index does take up space: every index you create takes up roughly the same space as a table consisting of the indexed fields. Creating indexes on char and text fields is not really a good idea; they work best on fixed length number fields.

And, yes, I did mean that a value in a field specified as unique cannot be repeated.

Some sample code for creating a composite primary key is:

PHP Code:

CREATE TABLE invoice_ln (
 invoice_number INTEGER(4) UNSIGNED ZEROFILL NOT NULL,
 invoice_line_number INTEGER(2) UNSIGNED ZEROFILL NOT NULL,
 descr TEXT,
 PRIMARY KEY (invoice_number, invoice_line_number)
); 


Hi sir
I'm confused on the space acquired when the index is created. Now my question is whether an index will occupy space or not.

Can you plz explain the below statement provided in detail.Could not understand that ??

Also, building an index does take up space: every index you create takes up roughly the same space as a table consisting of the indexed fields.

[Fumigator]

An index is a physical list of the values in that table's column. It occupies its own space in the database. It's as real as the table, and is separate from the table.

So if you create an index on table1.create_date, for example, then a file will be created that consists of one row for every row in table1. Each row contains the create_date and a pointer back to the row in table1 that the index's row belongs to.

Every time you update, delete from, or insert into table1, the index is also updated, deleted from, or inserted into.