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 [EMAIL PROTECTED] 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:[EMAIL PROTECTED] > Sent: Tuesday, April 26, 2005 6:50 AM > To: Jigal van Hemert; mysql@lists.mysql.com > 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/[EMAIL PROTECTED] > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] >
