Thread: foreign key constraint

foreign key constraint

From
Dennis Gearon
Date:
Is there any way to add a foreign key constraint after a table is created? I got this error
-----------------
PostgreSQL said: ERROR: parser: parse error at or near "FOREIGN"
Your query:
ALTER TABLE testUsrEmails ADD CONSTRAINT FOREIGN KEY (usr_id) REFERENCES testUsrs (usr_id)
-----------------


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:

-----------------
PostgreSQL said: ERROR: Relation "testusremails" does not exist (it's the next table in the
creation list)
Your query:
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,
sur_name text NOT NULL,
first_name text NOT NULL,
middle_name text DEFAULT 'none' NOT NULL,
created timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
FOREIGN KEY (usr_email_id_pri) REFERENCES testUsrEmails (usr_email_id) DEFERRABLE INITIALLY
DEFERRED);
-----------------





Re: foreign key constraint

From
Tom Lane
Date:
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

Re: foreign key constraint

From
Stephan Szabo
Date:
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. :)


Re: foreign key constraint

From
Dennis Gearon
Date:
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'.



Re: foreign key constraint

From
Dennis Gearon
Date:
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:

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




Re: foreign key constraint

From
Tom Lane
Date:
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

Re: foreign key constraint

From
Tom Lane
Date:
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