Re: foreign key constraint - Mailing list pgsql-general

Well,
    I discovered four things:

    a/ The dump of a table on my sytem does NOT show the foreign keys. Somewhere I heard that
that comes out of a full dump as ALTER statements. I think it should also show up as ALTER
statements on a table schema dump.

    b/ This mutually referred foreign constraint thing is touchy, BUT IT WORKS.

    c/ I had to make the FOREIGN KEY references in BOTH tables deferrable, not just the one I
inserted first, and I just made them INITIALLY DEFERRED to begin with, (maybe I didn't need to?)

    d/ I originally had SET CONSTRAINTS ALL DEFERRED in my function, but I tried it without
that and it works fine.

================================================================================
Thank you everybody for all your help with this over the last 3 weeks as I've learned from the
ground up. So somebody else can find it in the archives if they wish, here is all the tables and
functions I tested on, which worked.
================================================================================

--------------------------------------------------------------------------------
THE TABLE DEFINITIONS, IN THE ORDER TO CREATE THEM.
--------------------------------------------------------------------------------
CREATE TABLE testEmails(
email_id serial NOT NULL PRIMARY KEY,
email varchar(320) NOT NULL UNIQUE,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE TABLE testUsrEmailTypes(
usr_email_type_id serial NOT NULL PRIMARY KEY,
usr_email_type varchar(16) NOT NULL UNIQUE,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE TABLE testUsrs(
usr_id serial NOT NULL PRIMARY KEY,
usr_email_id_pri int4 NOT NULL,
login varchar(32) NOT NULL UNIQUE,
hashed_pw text NOT NULL,
first_name text NOT NULL,
middle_name text DEFAULT 'none' NOT NULL,
sur_name text NOT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL);

CREATE TABLE testUsrEmails(
usr_email_id serial NOT NULL PRIMARY KEY,
usr_id int4 NOT NULL,
email_id int4 NOT NULL,
usr_email_type_id int4 NOT NULL,
verify_id varchar(64) NOT NULL UNIQUE,
verified timestamp DEFAULT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
FOREIGN KEY (email_id) REFERENCES testEmails (email_id) DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (usr_id) REFERENCES testUsrs (usr_id) DEFERRABLE INITIALLY DEFERRED,
FOREIGN KEY (usr_email_type_id) REFERENCES testUsrEmailTypes (usr_email_type_id) DEFERRABLE
INITIALLY DEFERRED);

CREATE UNIQUE INDEX No_Dupe_UsrEmails_For_Uniqueness ON testUsrs (usr_email_id_pri);

ALTER TABLE testUsrs
   ADD CONSTRAINT FK_must_have_one_email
   FOREIGN KEY (usr_email_id_pri)
   REFERENCES testUsrEmails (usr_email_id) DEFERRABLE INITIALLY DEFERRED;

--------------------------------------------------------------------------------
FUNCTION TO ADD USRS, will create 'Email' record if one does not exists
--------------------------------------------------------------------------------
CREATE OR REPLACE FUNCTION test_add_usr ( testUsrs.login%TYPE,
                                          testUsrs.hashed_pw%TYPE,
                                          testUsrs.first_name%TYPE,
                                          testUsrs.middle_name%TYPE,
                                          testUsrs.sur_name%TYPE,
                                          testEmails.email%TYPE,
                                          testUsrEmailTypes.usr_email_type%TYPE,
                                          testUsrEmails.verify_id%TYPE
                                        )
RETURNS BOOLEAN AS '
DECLARE
    ret_val BOOLEAN := ''t''::BOOLEAN;

    arg_login               ALIAS FOR $1;
    arg_hashed_pw           ALIAS FOR $2;
    arg_first_name          ALIAS FOR $3;
    arg_middle_name         ALIAS FOR $4;
    arg_sur_name            ALIAS FOR $5;
    arg_email               ALIAS FOR $6;
    arg_usr_email_type      ALIAS FOR $7;
    arg_verify_id           ALIAS FOR $8;

    var_login               varchar;
    var_hashed_pw           text;
    var_first_name          text;
    var_middle_name         text;
    var_sur_name            text;
    var_email               varchar;
    var_usr_email_type      varchar;
    var_verify_id           varchar;

    var_email_id            int4;
    var_usr_id              int4;
    var_usr_email_id_pri    int4;

    BEGIN
-- verify good name values

    var_login               := trim( both FROM arg_login );
    var_hashed_pw           := trim( both FROM arg_hashed_pw );
    var_first_name          := trim( both FROM arg_first_name);
    var_middle_name         := trim( both FROM arg_middle_name);
    var_sur_name            := trim( both FROM arg_sur_name );
    var_email               := trim( both FROM arg_email );
    var_usr_email_type      := trim( both FROM arg_usr_email_type );
    var_verify_id           := trim( both FROM arg_verify_id );

    IF    ( (var_login IS NULL ) OR (octet_length( var_login )) < 1) THEN
        RAISE EXCEPTION ''ERROR - INVALID login IN STORED PROC add_usr'';
        ret_val := ''f''::BOOLEAN;

    ELSIF ( (var_hashed_pw IS NULL ) OR (octet_length( var_hashed_pw )) < 1) THEN
       RAISE EXCEPTION ''ERROR - INVALID hashed_pw IN STORED PROC add_usr'';
       ret_val := ''f''::BOOLEAN;

    ELSIF ( (var_first_name  IS NULL ) OR (octet_length( var_first_name )) < 1) THEN
        RAISE EXCEPTION ''ERROR - INVALID first_name argument IN STORED PROC add_usr'';
        ret_val := ''f''::BOOLEAN;

    ELSIF ( (var_middle_name IS NULL ) OR (octet_length( var_middle_name )) < 1) THEN
        RAISE EXCEPTION ''ERROR - INVALID middle_name argument IN STORED PROC add_usr'';
        ret_val := ''f''::BOOLEAN;

    ELSIF ( (var_sur_name IS NULL ) OR (octet_length( var_sur_name )) < 1) THEN
        RAISE EXCEPTION ''ERROR - INVALID sur_name argument IN STORED PROC add_usr'';
        ret_val := ''f''::BOOLEAN;

    ELSIF ( (var_email IS NULL ) OR (octet_length( var_email )) < 1) THEN
        RAISE EXCEPTION ''ERROR - INVALID email IN STORED PROC add_usr'';
        ret_val := ''f''::BOOLEAN;

    ELSIF ( (var_usr_email_type IS NULL ) OR (octet_length( var_usr_email_type )) < 1) THEN
       RAISE EXCEPTION ''ERROR - INVALID usr_email_type IN STORED PROC add_usr'';
       ret_val := ''f''::BOOLEAN;

    ELSIF ( (var_verify_id IS NULL ) OR (octet_length( var_verify_id )) < 1) THEN
       RAISE EXCEPTION ''ERROR - INVALID verify_id IN STORED PROC add_usr'';
       ret_val := ''f''::BOOLEAN;

    ELSE

-- insert good data

        INSERT INTO testUsrs( usr_email_id_pri,
                          login,
                          hashed_pw,
                          first_name,
                          middle_name,
                          sur_name )
                VALUES  ( -1, -- this line is discussed at bottom of this email
                          var_login,
                          var_hashed_pw,
                          var_first_name,
                          var_middle_name,
                          var_sur_name
                        );

        var_usr_id := currval( ''testusrs_usr_id_seq'' );

        IF (SELECT COUNT(*) FROM testEmails WHERE email = var_email ) < 1 THEN
           INSERT INTO testEmails( email ) VALUES ( var_email );
           var_email_id := currval( ''testemails_email_id_seq'' );
        ELSE
           var_email_id := (SELECT email_id FROM testEmails WHERE email = var_email );
        END IF;

        INSERT INTO testUsrEmails ( usr_id,
                                    email_id,
                                    usr_email_type_id,
                                    verify_id
                                  )
                            VALUES( var_usr_id,
                                    var_email_id,
                                    (SELECT usr_email_type_id
                                        FROM testUsrEmailTypes
                                        WHERE usr_email_type = var_usr_email_type
                                    ),
                                    var_verify_id
                                  );
            var_usr_email_id_pri := currval( ''testusremails_usr_email_id_seq'' );

            UPDATE testUsrs
               SET usr_email_id_pri = var_usr_email_id_pri
               WHERE usr_id = var_usr_id;

            ret_val := ''t'';

        END IF;
        RETURN ret_val;
    END;
' LANGUAGE 'plpgsql';

--------------------------------------------------------------------------------
TEST INVOCATION OF FUNCTION
--------------------------------------------------------------------------------
-- increment the two occurrences of '3' for each invocation to try multiple times

SELECT test_add_usr (
   'dude3'::varchar,
   'dfksdlsljfl;sdlk'::text,
   'Dennis'::text,
   'Keith'::text,
   'Gearon'::text,
   'gearond@cvc.net'::varchar,
   'Home'::varchar,
   'lkdfjsakjsalkjs3'::varchar
)
AS
did_it_work;

--------------------------------------------------------------------------------
FINAL NOTE
--------------------------------------------------------------------------------

In a concurrent environment, there would be a problem inserting the '-1' as it would violate the
unique constraint on that field if two processes were using this function. I will probably
implement a separate sequence and insert the negative value of that sequence. I would use
negative to show NO connection possible with any 'testUsrEmails'.