Re: Foreign Keys and OIDs

Toshio Kuratomi wrote:
>
> Is the foreign key mechanism supposed to work with OIDs?
> Is it known to be broken in 7.0.3?
>
> I declare two tables:
>
> CREATE TABLE address (
> street text,
> city text,
> state char(2) check ( state ~'[A-Z]{2}')
> );
>
> CREATE TABLE personaddress (
> person_id integer,
> address_id oid references address(oid),
> notes text,
> primary key (person_id, address_id)
> );
>
> Then I insert data into both address and person.
> When I try to insert into personaddress I get the following reply:
>
> =>INSERT INTO personaddress VALUES ('1', '29321', 'My home');
> ERROR:  constraint <unnamed>: table address does not have an attribute oid
>
> Should I create a new primary key column or is there some workaround or a
> fix in the upcoming release or am I just plain doing something wrong?
>
> Thanks,
> Toshio Kuratomi
> --
>
> ______S______U______B______L______I______M______I______N______A______L______
>     b  a  d  g  e  r  @  p  r  t  r  -  1  3  .  u  c  s  c  .  e  d  u
>                                                                 GA->ME 1999




The correct is:

CREATE TABLE address (
address_id  SERIAL PRIMARY KEY,
street text,
city text,
state char(2) check ( state ~'[A-Z]{2}')
);

CREATE TABLE personaddress (
person_id integer,
address_id int4 references address,
notes text,
primary key (person_id, address_id)
);


--
======================================================
AKACIA TECNOLOGIA
Desenvolvimento de sistemas para Internet
www.akacia.com.br