Thread: referencial integrity constraint bug in version 7.0 beta 5

I am running a slackware 7 linux box (kernel 2.2.14 g++2.91.66) and i 've
installed the ver 7 beta 5. So i had the following problem

I suppose when you make a constraint statement for reference key theere
should be a check for the fields names , example:

create table tbl1 (p1 int2 primary key,p2 int4);
and:
create table tbl2(p1 int2 primary key, p2 int2, constraint lala foreign
key (p2) references tbl1(po1));
(the o in po1 was misstyped)
there was no error message( i didn't noticed it too)
when i 've tryed the :
insert into tbl1 values (1,1);
i got the the error that the there was no field 'po1' for the constraint
procedure to check.
maybe that's because it's a beta version ........
if this was reported earlier excuse me but i am new to the list

Spyros Vasileiadis
Salonika Greece

Spyros Vasileiadis wrote:

> I am running a slackware 7 linux box (kernel 2.2.14 g++2.91.66) and i 've
> installed the ver 7 beta 5. So i had the following problem
>
> I suppose when you make a constraint statement for reference key theere
> should be a check for the fields names , example:

    You're  right. Bruce, would you please add these items to the
    7.1 todo:

        Check PK attributes to exist (maybe to be comparable with
        corresponding  FK  attribute  if possible) at foreign key
        definition time.

        Deny column dropping if column in question is used by  an
        FK constraint.

        Propagate column or table renaming to FK constraints.

        Emit  a  warning  if  at FK creation time no UNIQUE index
        exists over referenced PK attributes.


Jan

--

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

Done and updated.


> Spyros Vasileiadis wrote:
>
> > I am running a slackware 7 linux box (kernel 2.2.14 g++2.91.66) and i 've
> > installed the ver 7 beta 5. So i had the following problem
> >
> > I suppose when you make a constraint statement for reference key theere
> > should be a check for the fields names , example:
>
>     You're  right. Bruce, would you please add these items to the
>     7.1 todo:
>
>         Check PK attributes to exist (maybe to be comparable with
>         corresponding  FK  attribute  if possible) at foreign key
>         definition time.
>
>         Deny column dropping if column in question is used by  an
>         FK constraint.
>
>         Propagate column or table renaming to FK constraints.
>
>         Emit  a  warning  if  at FK creation time no UNIQUE index
>         exists over referenced PK attributes.
>
>
> Jan
>
> --
>
> #======================================================================#
> # It's easier to get forgiveness for being wrong than for being right. #
> # Let's break this rule - forgive me.                                  #
> #========================================= wieck@debis.com (Jan Wieck) #
>
>
>


--
  Bruce Momjian                        |  http://www.op.net/~candle
  pgman@candle.pha.pa.us               |  (610) 853-3000
  +  If your life is a hard drive,     |  830 Blythe Avenue
  +  Christ can be your backup.        |  Drexel Hill, Pennsylvania 19026

Jan Wieck writes:

>         Deny column dropping if column in question is used by  an
>         FK constraint.

Actually, all the DROP commands in SQL have a RESTRICT/CASCADE choice that
ought to take care of this. Unfortunately, the PostgreSQL behavior is more
like `dangle' with an occasional `restrict' but nothing really consistent.

>         Propagate column or table renaming to FK constraints.

Don't you refer to them by oid?

>         Emit  a  warning  if  at FK creation time no UNIQUE index
>         exists over referenced PK attributes.

That should actually be an error. (SQL clause 11.8 Syntax rule 2)


--
Peter Eisentraut                  Sernanders väg 10:115
peter_e@gmx.net                   75262 Uppsala
http://yi.org/peter-e/            Sweden

Peter Eisentraut wrote:
> Jan Wieck writes:
>
> >         Deny column dropping if column in question is used by  an
> >         FK constraint.
>
> Actually, all the DROP commands in SQL have a RESTRICT/CASCADE choice that
> ought to take care of this. Unfortunately, the PostgreSQL behavior is more
> like `dangle' with an occasional `restrict' but nothing really consistent.
>
> >         Propagate column or table renaming to FK constraints.
>
> Don't you refer to them by oid?

    No,  they  are  table and attribute name strings given to the
    trigger as arguments.

> >         Emit  a  warning  if  at FK creation time no UNIQUE index
> >         exists over referenced PK attributes.
>
> That should actually be an error. (SQL clause 11.8 Syntax rule 2)

    Yes, I know. And then we need to deny index dropping while FK
    constraints need it in last consequence.

    That'd  force  the user to require dropping/recreating all FK
    constraints if he needs to repair (i.e. drop/recreate) one of
    those indices. That's why I suggest some non-compliance here.


Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #