Primary key guarantees: duplicates and nullity

Question

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?

Answer 1

Oracle allows primary keys to have duplicate and null values.

Not really. What you have managed to create - after a series of complicated statements - is an enabled but not validated constraint. Which means that Oracle will check inserts and updates (for uniqueness) but there may be left existing duplicates. So it is a PK only in name. It's a not-validated constraint so not really a PK.

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)?

More context: I am working on an application that displays records and allows a user to delete them on any arbitrary table. When dealing with an arbitrary table, I need to use metadata and other means to determine which guarantees I have. Much database development is about guarantees -- guarantees that a large transaction will commit or not (but not partially commit). Guarantees that a successfully modified row stays modified. And guarantees that a primary key uniquely refers to exactly one row.

Yes, you can rely but only if your application reads the metadata and all the details - which may differ from DBMS to DBMS.

• SQL Server allows for disabled constraints? The application has to consider this when reading and interpreting the metadata tables.

• Oracle allows for disabled or non-validated constraints? The application has to consider those options too, accordingly.

• Postgres allows for some different weird scenarios? It has to consider them, too.

Another possible option - which may or may not be an option for you - is if the application is the only application that creates, deletes and modifies database objects or if all applications that do so are under your or a single control. Then it can rely on the soundness of the metadata ("consider only unique constraints") without checking for these details / rare cases - because it can rely that no such rare case is ever created in the first place.

With your conclusions:

Is it Oracle's fault? No.

I agree. I don't know why this was allowed but it probably solves some problem. And it probably was meant to be used only temporarily - e.g. during an import from another source to a database.

Is the table properly designed? No.

Are the statements to create such a table complex? Immaterial.

I agree, too, on both.

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.

We come to the same conclusion: needs to add a check for validation.

---

As for what other possibilities exist in SQL Server and Postgres:

• SQL Server:

  • PRIMARY KEY and UNIQUE constraints cannot be disabled.
  • Unique indexes can be disabled. They can also be re-enabled without check which would result in a situation very similar to the "enabled non-validated" in Oracle.
  • FOREIGN KEY and CHECK constraints can be disabled. They can be re-enabled without check, too.
    See ALTER TABLE and Disable indexes and constraints for details. My answer in this question: What is a WITH CHECK CHECK CONSTRAINT? explains options and the syntax, which differs from Oracle's.

• PostgreSQL:

  • PRIMARY KEY, UNIQUE and EXCLUDE constraints cannot be disabled.
  • FOREIGN KEY and CHECK constraints cannot be disabled. They can be created with the NOT VALID option though (which means that they are enabled without checking existing rows, as in SQL Server). See ALTER TABLE for details.

Answer 2

Within SQL Server a primary key is unique within the confines of the object, and cannot contain null values (in fact the column must be created with a not null constraint in order for it to be used in a primary key).

It is possible to create unique constraints, which will be unique, but will also allow for a null value.

Answer 3

Quoting PostgreSQL documentation on Primary Keys:

5.3.4. Primary Keys

A primary key constraint indicates that a column, or group of columns, can be used as a unique identifier for rows in the table. This requires that the values be both unique and not null. So, the following two table definitions accept the same data

[...]

Adding a primary key will automatically create a unique B-tree index on the column or group of columns listed in the primary key, and will force the column(s) to be marked NOT NULL.

So, in practice, it agrees with MS SQL Server; and the 2008 SQL Standard (draft from 2006):

A primary key constraint is a unique constraint that specifies PRIMARY KEY. A primary key constraint is satisfied if and only if no two rows in a table have the same non-null values in the unique columns and none of the values in the specified column or columns are the null value.