Oracle allows primary keys to have duplicate and null values. Using this capability isn't a particularly good idea, but it implies that some of what most developers consider a primary key's guarantees (non-null, unique) are not really guarantees.
CREATE TABLE oracle_guarantees (
ID NUMBER(9,0),
NAME VARCHAR2(50 BYTE),
breed NVARCHAR2(100)
);
INSERT INTO oracle_guarantees VALUES (1, 'Fuzz Head', 'Tabby');
INSERT INTO oracle_guarantees VALUES (2, 'Fluffy Thing', 'Mix');
INSERT INTO oracle_guarantees VALUES (2, 'Fluffy Thing', 'Mix');
INSERT INTO oracle_guarantees VALUES (3, 'Tiger', 'Tabby');
INSERT INTO oracle_guarantees VALUES (4, 'Fur Beast', 'Bengal');
INSERT INTO oracle_guarantees VALUES (5, 'Karate', 'Japanese Bobtail');
INSERT INTO oracle_guarantees VALUES (6, 'Chairman Meow', 'Chinese Harlequin');
INSERT INTO oracle_guarantees VALUES (NULL, 'No Cat', 'No breed');
CREATE INDEX oracle_guarantees_pk ON oracle_guarantees (ID);
ALTER TABLE oracle_guarantees ADD CONSTRAINT oracle_guarantees_pk PRIMARY KEY (ID) DISABLE KEEP INDEX;
ALTER TABLE oracle_guarantees MODIFY CONSTRAINT oracle_guarantees_pk ENABLE NOVALIDATE;
SQLite PKs also allow one null but not duplicate values.
Is it possible to have constraints marked as primary keys which have duplicate values or null values in Microsoft SQL or Postgres?
In other words, can I absolutely rely on PK uniqueness and non-nullity within the documented feature set (ignoring cases like manually edited data files, bugs, or modified server source code)?
Further clarification: I suspect that DBAs and developers work a little more differently than I thought.
Thought exercise: A developer needs to uniquely identify rows on any table (the table structure is unknown at compile time). Oracle's documentation on primary key constraints comes up in a search and says:
A primary key constraint combines a NOT NULL constraint and a unique constraint in a single declaration. That is, it prohibits multiple rows from having the same value in the same column or combination of columns and prohibits values from being null.
The developer then looks for a means to find the primary keys on any table at run time. They probably come across a question like this: https://stackoverflow.com/questions/9016578/how-to-get-primary-key-column-in-oracle
The top voted answer's query yields:
I believe that any reasonable non-DBA person would, at this point, conclude that the ID
column cannot have NULL or duplicate values. This conclusion is not true.
- Is it Oracle's fault? No.
- Is the table properly designed? No.
- Are the statements to create such a table complex? Immaterial.
- Is the Id column a "real" primary key? Maybe not, but this is more a philosophical matter.
ID
is listed (and is shown as "ENABLED") by the query in the top-rated SO answer to the linked question above. Perhaps that question needs to add a check for validation, but I have never once seen anyone check for this in code, nor do any answers in that thread or related threads I have found.
Ergo, in the real world, an arbitrary table can have a primary key (by definition of all_constraints.constraint_type
) which has duplicate values. And NULL values.
I now know that software must also ensure that the constraint is validated. Excellent! That provides the guarantee I need.
The question is: Is it possible to have a primary key (as defined by DBMS metadata) that has NULL or duplicate values in PostgreSQL or Microsoft SQL?