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

Re: Issue adding foreign key

From: Edmund Bacon <ebacon-xlii(at)onesystem(dot)com>
To: pgsql-general(at)postgresql(dot)org
Subject: Re: Issue adding foreign key
Date: 2004-10-28 20:59:00
Message-ID: m3is8usgob.fsf@elb_lx.onesystem.ca (view raw or flat)
Thread:
Lists: pgsql-general
george(dot)woodring(at)iglass(dot)net ("George Woodring") writes:

> I have 2 existing tables in my db:
> 
> iss=> \d pollgrpinfo
>              Table "public.pollgrpinfo"
>     Column     |          Type          | Modifiers
> ---------------+------------------------+-----------
>  pollgrpinfoid | integer                | not null
>  pollgrpid     | integer                | not null
>  name          | character varying(100) |
>  descript      | character varying(200) |
> Indexes:
>     "pollgrpinfo_pkey" primary key, btree (pollgrpinfoid)
>     "pollgrpinfo_pollgrpid_key" unique, btree (pollgrpid)
>  
> iss=> \d notpoll
>                    Table "public.notpoll"
>    Column    |          Type          |      Modifiers
> -------------+------------------------+---------------------
>  notpollid   | integer                | not null
>  pollgrpid   | integer                |
>  notgroupsid | integer                |
>  alerting    | character(1)           | default 'y'::bpchar
>  disuser     | character varying(50)  |
>  distime     | integer                |
>  alertingcom | character varying(200) |
> Indexes:
>     "notpoll_pkey" primary key, btree (notpollid)
>     "notpoll_pollgrpid_key" unique, btree (pollgrpid)
>     "notpoll_alerting_index" btree (alerting)
> Triggers:
>     "RI_ConstraintTrigger_2110326" AFTER INSERT OR UPDATE ON notpoll
> FROM notgroups NOT DEFERRABLE INITIALLY IMMEDIATE FOR EACH ROW EXECUTE
> PROCEDURE "RI_FKey_check_ins"('notgroups_exists', 'notpoll',
> 'notgroups', 'UNSPECIFIED', 'notgroupsid', 'notgroupsid')
> 
> I am trying to add a foreign key to the notpoll table
> 
> iss=> alter table notpoll add constraint pollgrp_exists foreign
> key(pollgrpid) references pollgrpinfo on delete cascade;
> ERROR:  insert or update on table "notpoll" violates foreign key
> constraint "pollgrp_exists"
> DETAIL:  Key (pollgrpid)=(7685) is not present in table "pollgrpinfo".
> 

When expressed as "FOREIGN KEY (foo) REFERENCES mytable"  postgresql
assumes that foo references the PRIMARY KEY  for table mytable. If you
are not referencing the primary key, you need to tell postgresql which
column  e.g. FOREIGN KEY (foo) REFERNCES mytable(foo). 


-- 
Remove -42 for email

In response to

pgsql-general by date

Next:From: CSNDate: 2004-10-28 21:08:21
Subject: Derived tables?
Previous:From: George WoodringDate: 2004-10-28 20:14:29
Subject: Issue adding foreign key

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