Deferrable Check Constraint In Postgresql

I have function checking mandatory participation as follows: CREATE FUNCTION member_in_has_address() RETURNS BOOLEAN AS $$ BEGIN RETURN EXISTS (SELECT * FROM address a, memb

Solution 1:

You can defer constraints in PostgreSQL in the same way as in other RDBMSs, but for current version (9.2) you can only defer UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES. Extract from this page of the manual:

DEFERRABLENOT DEFERRABLE

This controls whether the constraint can be deferred. A constraint that is not deferrable will be checked immediately after every command. Checking of constraints that are deferrable can be postponed until the end of the transaction (using the SET CONSTRAINTS command). NOT DEFERRABLE is the default. Currently, only UNIQUE, PRIMARY KEY, EXCLUDE, and REFERENCES (foreign key) constraints accept this clause. NOT NULL and CHECK constraints are not deferrable.

INITIALLY IMMEDIATEINITIALLY DEFERRED

If a constraint is deferrable, this clause specifies the default time to check the constraint. If the constraint is INITIALLY IMMEDIATE, it is checked after each statement. This is the default. If the constraint is INITIALLY DEFERRED, it is checked only at the end of the transaction. The constraint check time can be altered with the SET CONSTRAINTS command.

You can create a simple deferred foreign key from member_details to address instead of your current constraint to check, if every member has an address.

UPDATE: You need to create 2 foreign keys. One regular one from address(member_id) to member_details(member_id). The other one - deferred from member_details(member_id) to address(member_id).

With this two foreign keys you will be able to:

1. Create a member in member_details.
2. Create an address in address for member from step 1
3. Commit (with no errors)

OR

1. Create a member in member_details.
2. Commit (and get error from deferred foreign key).

Solution 2:

Wrap your queries in a transaction, and then use a deferred foreign key and deferred constraint triggers if at least one address is needed:

CREATECONSTRAINTTRIGGER member_details_address_check_ins
  AFTER INSERTON member_details
DEFERRABLE INITIALLY DEFERRED
FOREACHROWEXECUTEPROCEDURE member_details_address_check_ins();

ALTERTABLE address
ADDCONSTRAINT address_member_details_member_id_fkey
FOREIGN KEY (member_id) REFERENCES member_details(member_id)
ONUPDATENO ACTION ONDELETENO ACTION
DEFERRABLE INITIALLY DEFERRED;

CREATECONSTRAINTTRIGGER address_member_details_check_del
  AFTER DELETEON address
DEFERRABLE INITIALLY DEFERRED
FOREACHROWEXECUTEPROCEDURE address_member_details_check_del();

-- also consider the update cases for the inevitable merge of duplicate members.

On a separate note, normalized and pretty, but putting addresses and contact details such as emails in a separate address table occasionally introduces very colorful UI/UX issues. E.g. an untrained secretary changing the company and address of all of her boss' contacts at company A when one of them switched to company B. Yeah, seen it happen for real when the UI behaved differently from Outlook...

Anyway, and fwiw, I've found that it's usually more convenient to store this stuff in the same table as the contact, i.e. address1, address2, email1, email2, etc. It makes other things simpler for a variety of other reasons -- namely running checks like the one you're looking into. The extremely rare case where you'd want to store more than two such pieces of information are, in practice, simply not worth the hassle.

Solution 3:

This is what I come up with.

ALTERTABLE address
ADDCONSTRAINT address_member_in_has_address
FOREIGN KEY (member_id) REFERENCES member_details(member_id)
ONDELETE CASCADE
DEFERRABLE INITIALLY DEFERRED;

CREATEFUNCTION member_in_has_address() RETURNStriggerAS $BODY$
    BEGIN
    IF NOTEXISTS(SELECT*FROM member_details
                   WHERE member_id IN (SELECT member_id 
                                        FROM address)) 
    THEN
            RAISE EXCEPTION 'Error: member does not have address';
        END IF;
    RETURNNEW;
    END;
$BODY$ LANGUAGE plpgsql;

CREATECONSTRAINTTRIGGER manatory_participation_member_details_ins
 AFTER INSERTON member_details 
 DEFERRABLE INITIALLY DEFERRED 
 FOREACHROWEXECUTEPROCEDURE member_in_has_address();

CREATECONSTRAINTTRIGGER manatory_participation_member_details_del
 AFTER INSERTON member_details 
 DEFERRABLE INITIALLY DEFERRED 
 FOREACHROWEXECUTEPROCEDURE member_in_has_address();

I tried Igor's version using foreign keys in both tables without the triggers. In this case this constraint is not deffered.

ALTERTABLE member_details
ADDCONSTRAINT member_details_in_has_address
FOREIGN KEY (address_id) REFERENCES address
ONUPDATENO ACTION ONDELETE CASCADE
DEFERRABLE INITIALLY DEFERRED;

I get this: ERROR: null value in column "address_id" violates not-null constraint

When inserting using this annonymous block:

DO $$ 
DECLARE 
 mem BIGINT;
BEGININSERTINTO member_details (member_first_name, member_last_name, member_dob, member_phone_no, 
member_email, member_gender, industry_position, account_type, music_interests)
VALUES ('Rado','Luptak','07/09/80','07540962233','truba@azet.sk','M','DJ','basic','hard core');

SELECT member_id 
 INTO mem
FROM member_details
WHERE member_first_name ='Rado'AND member_last_name ='Luptak'AND member_dob ='07/09/76';

INSERTINTO address (address_id, house_name_no, post_code, street_name, town, country, member_id)
VALUES (mem, '243', 'E17 3TT','Wood Road','London', 'UK', mem);

UPDATE member_details
 SET  address_id = mem WHERE member_id = mem;
END
$$;

Another problem with enforcing mandatory participation in member_details using address_id of address table (Igor's version) is that this allows me to insert row into member_details and reference an existing address row, but the existing address row references different member_details row. When the latter member_details row is deleted it cascades and deletes the address row, which can or cannot delete (depends on settings) the new inserted member_details row. It would also return different details when joining on member_id and on address_id. Therefore, it requires another constraint, so I stayed with trigger and dropping it before insert and recreating it after insert, due to the trigger is not deferred.

Solution 4:

2 tested approaches.

1.Alter CONSTRAINTS INITIALLY DEFERRED.

begin;

alterTABLE t1 alterCONSTRAINT t1_fkey deferrable INITIALLY DEFERRED;

deletefrom t1;

-- insert into t1 (...)alterTABLE t1 alterCONSTRAINT t1_fkey not deferrable;

-- commit;rollback;

2.SET CONSTRAINTS ALL DEFERRED.

begin;

alterTABLE t1 alterCONSTRAINT t1_fkey deferrable initially immediate;
SET CONSTRAINTS t1_fkey DEFERRED
-- SET CONSTRAINTS ALL DEFERRED;  -- or, do this.deletefrom t1;

-- insert into t1 (...)alterTABLE t1 alterCONSTRAINT t1_fkey not deferrable;

-- commit;rollback;