Re: DEFERRABLE NOT NULL constraint

2013/2/5 Darren Duncan <darren(at)darrenduncan(dot)net>:
> I'd like to know what value there is in making NOT NULL and CHECK
> deferrable.

Consider such schema sample:
- you have tables “groups” and “group_items”
- each group must have at least one item
- each group must have a “master” item, that is denoted in
groups.master_item_id column
- groups.group_id, groups.master_item_id, group_items.item_id and
group_items.group_id should be NOT NULL
- you use “serial” type for the KEY columns

Now, when you're creating a new group:
- you cannot insert a row into the groups, as master_item_id is not
yet known and NULL is not allowed;
- you cannot insert a row into the group_items, as you need to know
group_id, FK can be deferred, but NULL is not allowed.

All this works pretty good if one don't use “serial” type for the keys and
explicitly calls nextval() on the corresponding sequences first.

--
Victor Y. Yegorov