Primary key guarantees: duplicates and nullity
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 (&quot;consider only unique constraints&quot;) 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 &quot;real&quot; primary key? Maybe not, but this is more a philosophical matter. ID is listed (and is shown as &quot;ENABLED&quot;) 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 &quot;enabled non-validated&quot; 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.