Re: [GENERAL] Issue adding foreign key

2004-10-31 Thread Edmund Bacon 
[EMAIL PROTECTED] (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

---(end of broadcast)---
TIP 5: Have you checked our extensive FAQ?

   http://www.postgresql.org/docs/faqs/FAQ.html

[GENERAL] Issue adding foreign key

2004-10-28 Thread George Woodring 
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.

I have verified that the information is in the pollgrpinfo table:

iss= select * from pollgrpinfo where pollgrpid=7685;
 pollgrpinfoid | pollgrpid | name | descript
---+---+--+--
   767 |  7685 | HTTP |
(1 row)


I could use a suggestion on how to proceed in troubleshooting the error
message.  I am running 7.4.5

Thanks,
Woody


iGLASS Networks
211-A S. Salem St
Apex NC 27502
(919) 387-3550 x813
www.iglass.net

---(end of broadcast)---
TIP 9: the planner will ignore your desire to choose an index scan if your
  joining column's datatypes do not match