Re: [GENERAL] Issue adding foreign key
[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
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