INSERT INTO t_simple (a,b) VALUES (42,NULL) Ok, so what about (42,NULL) - this is the part that I always found confusing about MATCH SIMPLE, - works
#Postgresql pgadmin 4 foreign key allows to delete data update#
The insert into t_full generates the following error, ERROR: insert or update on table "t_full" violates foreign key constraint "t_full_a_fkey"ĭETAIL: MATCH FULL does not allow mixing of null and nonnull key values. INSERT INTO t_full (a,b) VALUES (1,NULL) INSERT INTO t_simple (a,b) VALUES (1,NULL) The problem comes when one of the columns is NULL. Logically, with FULL and SIMPLE, we can insert a full match. one row with (1,1)įOREIGN KEY (a,b) REFERENCES foo MATCH FULLįOREIGN KEY (a,b) REFERENCES foo MATCH SIMPLE While the chosen answer is correct, if this is new to you, you may want to see it with code - I think it's easier to grok that way. Don't fall for outdated Google links to outdated versions. If you don't want referencing rows to be able toĪvoid satisfying the foreign key constraint, declare the referencingĪnd be sure to consult the current manual or the version matching your installation. (so a mix of null and non-null values is guaranteed to fail a MATCH FULLĬonstraint). Satisfying the constraint only if all its referencing columns are null Is added to the foreign key declaration, a referencing row escapes Normally, a referencing row need not satisfy the foreign keyĬonstraint if any of its referencing columns are null. The referencing column(s) to prevent these cases from arising.) (Of course, NOT NULL constraints can be applied to
Required to have a match in the referenced table. Key columns to be null if any of them are null, the row is not
MATCH FULL will not allow one column ofĪ multicolumn foreign key to be null unless all foreign key columnsĪre null if they are all null, the row is not required to have a There are three match types: MATCH FULL, MATCH PARTIAL, and MATCH SIMPLE Check the CREATE TABLE page of the manual:
0 kommentar(er)
