List:General Discussion« Previous MessageNext Message »
From:SGreen Date:April 26 2005 1:49pm
Subject:RE: why NOT NULL in PRIMARY key??
View as plain text  
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
> 

Thread
why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jochem van Dieten26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Harald Fuchs26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??SGreen26 Apr
      • Att. Shawn GreenJigal van Hemert2 May
  • Re: why NOT NULL in PRIMARY key??Dawid Kuroczko26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Jochem van Dieten26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Mikhail Entaltsev26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
  • Re: why NOT NULL in PRIMARY key??Harald Fuchs26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert27 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley27 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert27 Apr
    • Re: why NOT NULL in PRIMARY key??Frank Bax27 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley27 Apr
    • Re: why NOT NULL in PRIMARY key??Joerg Bruehe27 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert28 Apr
RE: why NOT NULL in PRIMARY key??Jay Blanchard26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • RE: why NOT NULL in PRIMARY key??Frank Bax26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert26 Apr
    • Re: why NOT NULL in PRIMARY key??Paul DuBois26 Apr
    • Re: why NOT NULL in PRIMARY key??Peter Brawley26 Apr
RE: why NOT NULL in PRIMARY key??emierzwa26 Apr
  • RE: why NOT NULL in PRIMARY key??SGreen26 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies26 Apr
RE: why NOT NULL in PRIMARY key??Jay Blanchard26 Apr
  • Re: why NOT NULL in PRIMARY key??Dawid Kuroczko27 Apr
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies27 Apr
RE: why NOT NULL in PRIMARY key??emierzwa26 Apr
Re: why NOT NULL in PRIMARY key??beacker30 Apr
  • Re: why NOT NULL in PRIMARY key??Jigal van Hemert1 May
  • Re: why NOT NULL in PRIMARY key??Martijn Tonies1 May