Re: foreign key constraint

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(at)cvc(dot)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'.