question

Kastaka avatar image
Kastaka asked

NULL in Primary Key columns?

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?

primary-keynullindex-constraints
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
Christian13467 avatar image
Christian13467 answered

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;

Unexpected.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.