Re: Foreign key error...

Derrick Betts wrote:
> [...]
> The command I attempted to execute was:
> alter table casenotes
> add constraint fk_casenotes foreign key (caseid) references
> public.clientinfo (caseid) on update cascade on delete cascade;
>
> The error returned was:
> ERROR: fk_casenotes referential integrity violation - key referenced from
> casenotes not found in clientinfo
>
> Why will it work on some tables and not others? The "failing" table was
> created in the same manner as the others.

The "failing" table probably already contains one or more
rows with caseid values which are not in the clientinfo
table. You cannot add a constraint if the table already
violates it. This command will tell you the rows which
need to be fixed or deleted:

select * from casenotes where caseid not in
(select caseid from clientinfo);

Regards
Oliver

--
Oliver Fromme, secnetix GmbH & Co KG, Oettingenstr. 2, 80538 München
Any opinions expressed in this message may be personal to the author
and may not necessarily reflect the opinions of secnetix in any way.

"File names are infinite in length, where infinity is set to 255 characters."
-- Peter Collinson, "The Unix File System"