foreign key constraint

Dennis Gearon <gearond@cvc.net> writes:

ALTER TABLE testUsrEmails ADD CONSTRAINT FOREIGN KEY (usr_id) REFERENCES testUsrs (usr_id)

Read the syntax more closely. You can say ... ADD FOREIGN KEY ...
or ... ADD CONSTRAINT constraint_name FOREIGN KEY ...

regards, tom lane

On Tue, 4 Mar 2003, Dennis Gearon wrote:

OR, how is it possible to add foreign key constraints, ('cross constraints'), between two tables.
I tried it in a transaction, but it didn't work. The first constraint in the table definition
was DEFERRABLE INITIALLY DEFERRED, but I don't think the parser cared :-) I got this error:

Tom dealt with the first part. I'll deal with this one. The initially
deferred refers to the the check time of the constraint, however to make
the constraint requires the table to be there. I think this is defensible
since you need permissions at the table constraint creation time on the
other table which is pretty much impossible if it doesn't exist. :)

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'.

Dennis Gearon <gearond@cvc.net> writes:

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.

It works that way for me...

regards, tom lane

Was there a version that did NOT show the ALTER statements when dumping a table?

This may be a problem with phpPgAdmin.

3/4/2003 2:11:48 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:

Show quoted text

Dennis Gearon <gearond@cvc.net> writes:

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.

It works that way for me...

regards, tom lane

Dennis Gearon <gearond@cvc.net> writes:

Was there a version that did NOT show the ALTER statements when dumping a table?

Before 7.3 (or was it 7.2?) foreign keys were dumped as triggers not
ALTER ADD FOREIGN KEY commands.

regards, tom lane