By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

Issue adding foreign key

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
ge*************@iglass.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
Nov 23 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.