CODEWITHSUNDEEP
postgresqlreferenceforeign-keysprimary-key
Alex
Alex

Reputation: 15708

Can foreign key references contain NULL values in PostgreSQL?

As an example

create table indexing_table
(
  id SERIAL PRIMARY KEY,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
);

Is there a difference between the following tables?

Table 1:

create table referencing_table
(
  indexing_table_id INTEGER references indexing_table
);

Table 2:

create table referencing_table
(
  indexing_table_id INTEGER references indexing_table NOT NULL
);

Alternatively, in the case of Table 1, where there is no NOT NULL constraint, are we allowed to insert records containing NULL values?

Upvotes: 46

Views: 50855

Answers (2)

richyen
richyen

Reputation: 9978

Sometimes you want a foreign keyed column to be nullable because it is not required (just as not every citizen in a citizens table went to a university, so a university_id column can be null). In other cases, the column should not be null, just as every student lshould be associated with a university_id.

Therefore, the two referencing_tables you describe are actually very different, if you consider what you are trying to achieve.

Upvotes: 22

Mike Sherrill 'Cat Recall'
Mike Sherrill 'Cat Recall'

Reputation: 95582

For table 1, this INSERT statement will succeed. If you run it 100 times, it will succeed 100 times.

insert into referencing_table values (null);

The same INSERT statement will fail on table 2.

ERROR:  null value in column "indexing_table_id" violates not-null constraint
DETAIL:  Failing row contains (null).

Upvotes: 55

Related Questions