I've been looking at constraint properties - deferred constraints, what happens when constraints are disabled and re-enabled - and I noticed a rather confusing property of some Primary Key constraints.
Normally, when I put a primary key constraint on a column when creating a table, it will make the column NOT NULL automatically. However, some of the constraint properties seem to make this not happen!
Does anyone know the comprehensive list of when a Primary Key column might unexpectedly be able to contain NULL values?
Answer by Christian13467 ·
A primary key column might unexpectedly contain null values if the primary key is disabled.
This oracle documentation says that a primary key can contain null values. But if you try to create a table with a primary key defined this column will always be not nullable.
-- Create Table CREATE TABLE a (a number primary key, b number); -- Check if constraints are there SELECT table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name = 'A'; -- Describe Table DESC a -- Disable Constraint ALTER TABLE a disable constraint xxxx; -- Fill in the right name -- Describe Table again DESC a
If you disable the constraint the column will be nullable.
Disable a constraint has no impact if the constraint is based on an index. The index will remain and support your queries.
If this index is a unique index it becomes curious. You can have more than one row having null values into your "primary key" columns. An oracle unique index checks for uniqueness of not null values.
-- Create Table CREATE TABLE a (a number primary key, b number unique); -- Check if constraints are there SELECT table_name, constraint_name, constraint_type FROM user_constraints WHERE table_name = 'A'; -- Insert data INSERT INTO a VALUES (1, 2); INSERT INTO a VALUES (2, null); INSERT INTO a VALUES (3, null); -- Check data SELECT * FROM a;