Ed,
UNIQUE indexes (which you quote) and PRIMARY KEYS are similar but are NOT
the same thing. Both types of keys guard against duplication of values for
the tuple defining the index. However, PRIMARY KEYs hold special
significance in that many RDBMS storage engines will use the PK to
uniquely identify each row and not use an internally generated ROWID (at
least that happens for InnoDB). PKs are critical to data integrity (and
usually internal table organization,too) and are frequently used as one
side of a Foreign Key (FK) relationship.
http://dev.mysql.com/doc/mysql/en/table-and-index.html
=========================================
If you do not define a PRIMARY KEY for your table, MySQL picks the first
UNIQUE index that has only NOT NULL columns as the primary key and InnoDB
uses it as the clustered index. If there is no such index in the table,
InnoDB internally generates a clustered index where the rows are ordered
by the row ID that InnoDB assigns to the rows in such a table.
=========================================
http://dev.mysql.com/doc/mysql/en/create-table.html
=========================================
In MySQL, a UNIQUE index is one in which all values in the index must be
distinct. An error occurs if you try to add a new row with a key that
matches an existing row. The exception to this is that if a column in the
index is allowed to contain NULL values, it can contain multiple NULL
values. This exception does not apply to BDB tables, for which an indexed
column allows only a single NULL.
=========================================
A PRIMARY KEY is a unique KEY where all key columns must be defined as
NOT NULL. If they are not explicitly declared as NOT NULL, MySQL declares
them so implicitly (and silently). A table can have only one PRIMARY KEY.
If you don't have a PRIMARY KEY and an application asks for the PRIMARY
KEY in your tables, MySQL returns the first UNIQUE index that has no NULL
columns as the PRIMARY KEY.
=========================================
Notice that UNIQUE indexes in MySQL allow multiple nullable columns.
However because of their critical importance to record identification,
PRIMARY KEYs cannot contain null values. This is true even for the other
systems you quoted
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/createdb/cm_8_des_04_888k.asp
(MSDN online documentation for MS SQL Server)
========================================
A table usually has a column or combination of columns whose values
uniquely identify each row in the table. This column (or columns) is
called the primary key of the table and enforces the entity integrity of
the table. You can create a primary key by defining a PRIMARY KEY
constraint when you create or alter a table.
A table can have only one PRIMARY KEY constraint, and a column that
participates in the PRIMARY KEY constraint cannot accept null values.
Because PRIMARY KEY constraints ensure unique data, they are often defined
for identity column.
=======================================
http://manuals.sybase.com/onlinebooks/group-as/asg1250e/sqlug/@Generic__BookTextView/21064
(online Sybase Transact-SQL User's guide)
=========================================
You can declare unique or primary key constraints to ensure that no two
rows in a table have the same values in the specified columns. Both
constraints create unique indexes to enforce this data integrity. However,
primary key constraints are more restrictive than unique constraints.
Columns with primary key constraints cannot contain a NULL value. You
normally use a table's primary key constraint in conjunction with
referential integrity constraints defined on other tables.
=========================================
Respectfully,
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
emierzwa@stripped wrote on 04/26/2005 09:16:03 AM:
> Not every DBMS...
>
> MSSQL:
> Create Unique Index
> Microsoft(r) SQL Server(tm) checks for duplicate values when the index
> is created (if data already exists) and checks each time data is added
> with an INSERT or UPDATE statement. If duplicate key values exist, the
> CREATE INDEX statement is canceled and an error message giving the first
> duplicate is returned. Multiple NULL values are considered duplicates
> when UNIQUE index is created.
>
>
> SYBASE:
> Create Unique Index
> Prohibits duplicate index (also called key) values. The system checks
> for duplicate key values when the index is created (if data already
> exists), and checks each time data is added with an insert or update. If
> there is a duplicate key value or if more than one row contains a null
> value, the command is aborted and an error message giving the duplicate
> is printed.
>
>
> Ed
>
> -----Original Message-----
> From: Jay Blanchard [mailto:jay.blanchard@stripped]
> Sent: Tuesday, April 26, 2005 6:50 AM
> To: Jigal van Hemert; mysql@stripped
> Subject: RE: why NOT NULL in PRIMARY key??
>
> Since NULL is the absence of a value and PRIMARY keys must have a value
> a NULL column cannot be included as a portion of a PRIMARY key. AFAIK
> this is the case with every RDBMS out there. Asking the development team
> might get you a more informative response.
>
> There is a vast difference between a zero value, blank value and NULL
> (absence of value).
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:
> http://lists.mysql.com/mysql?unsub=1
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1
>
