Primary key guarantees: duplicates and nullity

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
ro

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.

StackExchange Database Administrators Q#171567, answer score: 8