Take this example of a column definition:
permission_id int not null references ugp_permissions (id)
Is the not null constraint redundant in this example? I think it is, since the provided value has to match on the referenced table. However, when there are null records on the referenced column, would that classify as a match for the foreign key?
It’s not redundant in this situation because such a constraint prevents omission of a permission_id. 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.
In this situation, the permission_id is required because it is a not null column
Disclosure: I work for EnterpriseDB (EDB)
When you omit "not null" the value can be null, but when it is not null it's checked against the referenced table.
No, if there are null records on the referenced column, they will not match for the foreign key.
