Re: Re: 7.1.2 ERROR: UNIQUE constraint matching given keys for referenced table ......

On Tue, 26 Jun 2001, Lincy wrote:

> Ljb :
>
>     Thanks your help. Look like add the UNIQUE constraint on it can solve
> this problem. Now I know what the problem is. But since our system must
> handle multi-company in same time. So we can't let department to be UNIQUE.
> What we try to using references in EmployeeInfo are just for check the
> department was exist or not. So, we will try to using trigger to do this
> check. Well, if need to write our own trigger for each table, we have lots
> work to do.   :(

If department can't be unique (presumably because two companies could have
the same department name) then wouldn't you want to know what company the
employee and department belong to?  If so, then the key should be both the
company and department (unless you're allowing one company to have
multiple departments of the same name) which would then be legally unique
and referenceable.