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

Thanks, I have change my code as you suggest. :)

-----Original Message-----
From: Stephan Szabo [mailto:sszabo(at)megazone23(dot)bigpanda(dot)com]
Sent: Thursday, July 05, 2001 3:39 AM
To: Lincy Lin
Cc: pgsql-general(at)postgresql(dot)org
Subject: RE: [GENERAL] Re: 7.1.2 ERROR: UNIQUE constraint matching given keys for referenced table ......

On Wed, 4 Jul 2001, Lincy Lin wrote:

> Thanks your reply.
>
> Actully, We are using 4 fields (CompanySerialNo, BrahcnOffice,
> DepartmentCategory, Department) to check employee work for which
> company parimary. Well, we have many company here (In Taiwan) have
> multi-company name but in same office. Some employee work for all
> multi-company. But our system only allow one employee belong to one
> company in this time.
>
> Create Table CompanyInfo
> (
> CompanySerialNo Char(20) Parimary Key,
> LocalName Text,
> EnglishName Text,
> .............................
> .............................
> );
>
> Create Table DepartmentInfo
> (
> CompanySerialNo Char(20) References CompanyInfo
> On Update Cascade
> On Delete Cascade,
> BranchOffice Char(40) Default '',
> DepartmentCategory Char(40) Default '',
> Department Char(40) Default ''
> /* Primary Key (CompanySerialNo, BranchOffice,
> DepartmentCategory, Department) */ );
>
> Create Table EmployeeInfo
> (
> EmployeeNo Char(20) Primary Key,
> LocalName Text,
> FirstName Text,
> LastName Text,
> ...........................
> ...........................
> CompanySerialNo Char(20) References CompanyInfo
> On Update Cascade
> On Delete Cascade,
> BranchOffice Char(40), /* References DepartmentInfo (BranchOffice) */
> /* On Update Cascade */
> /* On Delete Set Default, */
> DepartmentCategory Char(40), /* References DepartmentInfo (DepartmentCategory)*/
> /* On Update Cascade */
> /* On Delete Set Default, */
> Department Char(40), /* References DepartmentInfo (Department) */
> /* On Update Cascade */
> /* On Delete Set Default, */
> ...........................
> ...........................
> );
>
> If
>
> 1. one company don't allow multi-department with same name
> How to set the references in EmployeeInfo table ?

>
> 2. one company allow multi-department with same name (But not
> with same BranchOffice and/or DepartmentCategory).
> How to set the references in EmployeeInfo table ?

Okay, since it looks like your key in DepartmentInfo is
> /* Primary Key (CompanySerialNo, BranchOffice,
> DepartmentCategory, Department) */ );

You should probably make sure the primary key is defined in
DepartmentInfo and do something like

FOREIGN KEY (CompanySerialNo, BranchOffice, DepartmentCategory,
Department) REFERENCES DepartmentInfo

as a table constraint in EmployeeInfo.

> I think the postgresql from 7.0.3 to 7.1.2 have change some
> basic rule. The old code work fine in 7.0.3 but not ok in 7.1.2.

Yeah, 7.0.3 was technically broken. The spec requires the unique
constraint, but we didn't check it yet.