Skip site navigation (1) Skip section navigation (2)

Re: 7.0 FK trigger question

From: wieck(at)debis(dot)com (Jan Wieck)
To: Adriaan Joubert <a(dot)joubert(at)albourne(dot)com>
Cc: Jan Wieck <wieck(at)debis(dot)com>, Postgresql <pgsql-hackers(at)postgresql(dot)org>
Subject: Re: 7.0 FK trigger question
Date: 2000-04-05 11:50:43
Message-ID: m12coKZ-0003knC@orion.SAPserv.Hamburg.dsh.de (view raw or flat)
Thread:
Lists: pgsql-hackers
> Thanks for the reply. For the time begin I've solved this by copying every
> table in the database to a backup table without any constraints, recreating
> the tables and copying the data back in. I have to be a bit careful with
> doing it all in the right order, although I think I can solve this by doing
> everything in a transaction as the constraints are only checked at the end of
> transaction?

    By  default,  constraints  are  checked  at end of statement.
    Constraints can be specified DEFERRABLE, then you can do  SET
    CONSTRAINTS  ... DEFERRED which will delay them until COMMIT.

> OK, I'm definitely not being very bright here, but i cannot get my system to
> accept the alter column commands. An example on the man pages ,ay help a lot
> here! I tried
>
> test=# create table t (i int4);
> CREATE
> test=# create table t1 (k int4);
> CREATE
> test=# alter table t1 alter column k add constraint references t(i);
> ERROR:  parser: parse error at or near "add"
> test=# alter table t1 alter column k constraint references t(i);
> ERROR:  parser: parse error at or near "constraint"
> test=# alter table t1 alter k constraint references t(i);
> ERROR:  parser: parse error at or near "constraint"
> test=# alter table t1 alter column k create constraint references t(i);
> ERROR:  parser: parse error at or near "create"
>
> So what am I doing wrong?

    alter table t1 add constraint chk_k foreign key (k) references t (i);

    The referenced column(s) (t.i in your case above) must not be
    a  primary  key  -  any combination is accepted. SQL standard
    requires that  there  is  a  unique  index  defined  for  the
    referenced  columns so it is guaranteed that FKs reference to
    exactly ONE row. Actually Postgres doesn't check or force it,
    so you have to take care yourself.  For example:

    create table t (i integer, j integer);
    create unique index t_pk_idx_1 on t (i, j);    -- DON'T FORGET THIS!
    create table t1 (k integer, l integer,
        foreign key (k, l) references t (i, j));

    BTW: all existing data is checked at ALTER TABLE time.

    And  our  implementation  of  FK is based on SQL3. So you can
    specify match  type  FULL  (PARTIAL  will  be  in  7.1),  and
    referential  actions (ON DELETE CASCADE etc.) too. It is nice
    to define ON UPDATE CASCADE, because if you UPDATE a PK,  all
    referencing FKs will silently follow then.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#========================================= wieck(at)debis(dot)com (Jan Wieck) #



In response to

pgsql-hackers by date

Next:From: Jan WieckDate: 2000-04-05 12:20:10
Subject: Re: [INTERFACES] refint doesn't work well with BDE (fwd)
Previous:From: Karel ZakDate: 2000-04-05 11:33:56
Subject: Re: caching query results

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group