The testing is required! -- Run the following SQL code in the database you choose: DROP TABLE IF EXISTS tblChild; DROP TABLE IF EXISTS tblParent; CREATE TABLE tblParent  (    p_id        CHAR(16),    p_active    BOOLEAN DEFAULT TRUE,    CONSTRAINT parent_PK PRIMARY KEY(p_id) ); CREATE TABLE tblChild (    c_id        CHAR(16),    p_id        CHAR(16),    CONSTRAINT child_PK PRIMARY KEY(c_id, p_id), -- note two field PK!    --    CONSTRAINT parent_FK FOREIGN KEY(p_id) REFERENCES tblParent(p_id) );  Part 1 Create a procedure named procInsertParent that takes one input parameter compatable with p_id and will create a row in the tblParent table.  It will also receive a SMALLINT as a second parameter in which it will pass the result of the operation back to the calling routine.   Check for a NULL parameter violation. If the p_id already exists, check the p_active field.  If that is TRUE, return a PK violation code. If the p_id is there and p_active=FALSE UPDATE p_active to TRUE.  If it doesn't exist, INSERT it.  In either latter case, return a success code.     Condition                           return error code     Success                             0        Null Parameter                      1        PK violation (exists & active)      2 In the EXCEPTION block, just RAISE a message that an exception happened.  Advice:    First check for a NULL parameter, that's easy.        Have a local BOOLEAN variable.  For example: lvActive            SELECT p_active INTO lvActive        FROM tblParent        WHERE p_id=parmYourParametersName;            Now, in pgSQL, a BOOLEAN has one of *three* values!    TRUE, FALSE, or NULL.  Its value is all you need to decide what to do!        I'd like to say:            IF lvActive IS NOT NULL AND lvActive=TRUE            But don't do that!  pgSQL does *not* short circuit the BOOLEAN expressions. You don't    know what will happen if it IS NULL because it's like division by zero.        Break it into two "IF" expressions:            IF lvActive IS NOT NULL        THEN            IF lvActive=TRUE            THEN    -- here you know it's a PK violation            ELSE    -- UPDATE the p_active field, success            END IF        ELSE            -- It's NULL, i.e.: not there, INSERT, success        END IF You don't *have* to use this logic, but it'll make your life easier. You may use a similar approach in the next procInsertChild procedure.            -- -------------------------- Testing:    Call the procedure to insert three different parents.  (Three returns of zero.)        Update the table to set one record p_active=FALSE.        Call procInsertParent with that record.  It should return zero and change it to TRUE.        Deliberately violate the NOT NULL.  (returns 1)        Deliberately violate the PK on any active record. (2)                         Part 2   Create a procedure named procInsertChild that takes two input parameters compatable with c_id and p_id respectively in that order.  The procedure will create a row in the tblChild table. It will also receive a SMALLINT as a third parameter in which it will pass the result of the operation.   There is a primary key violation if a record exists with *both* the c_id AND the p_id. If the p_id exists in the parent table, check p_active field; if that is FALSE, then fail the operation on a foreign key violation.  It only returns a success code when it has an active parent,  NULL or FALSE fails on an FK violation     Condition                           return error code     Success                             0        Either Parameter NULL               1        PK violation (already exists)       2        Foreign Key Violation               3    Testing    You should have three records in tblParent.  If not, insert them.  Cause one to be inactive.        Insert three valid records for at least two of the parents.  (Success)        Attempt a NULL insert for each parameter.  (1)        Attempt a PK violation (child & parent same) (2)        Attempt to insert a child for the inactive parent. (3)        Attempt to insert a bogus parent. (3)    */