Possible Addition of a CREATE TABLE Warning

Austin C. Brooks (acbrooks(at)southern(dot)edu) reports a bug with a severity of 4
The lower the number the more severe it is.

Short Description
Possible Addition of a CREATE TABLE Warning

Long Description
The enclosed CREATE TABLE statement attempts to create a not-nullable field that is a foreign key into another table. One of the foreign key constraints, however, is that the field be set null when the referenced tuple is deleted. Unfortunately, this action is not possible because the field is not-nullable, and thus the foreign key constraint will fail out every time. This is expected behavior, but it might be helpful to schema designers (or newbies, as I was) if a warning message indicated this possibility (ie, that the DBA would have to manually delete or alter every referencing tuple in the table before he can delete the referenced tuple). PostgreSQL 7.0.3 currently processes the table without complaint, which is fine for people that understand what is happening.

Sample Code

CREATE TABLE myTable2 (
...
myField int not null,
...
foreign key (myField) references (myTable1) on delete set null,
-- 'on delete set null' will fail every time
...
);

No file was uploaded with this report