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

Regrading your discussion, at the end, what is the difference between a
REFERENCE clause in a field definition and a FOREIGN KEY in the table
definition ? What is the best solution to implement a classical foreign
key in a table ?

thanks

Stephan Szabo wrote:

> 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.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/users-lounge/docs/faq.html

--
**************************************************
Eddie IANNUCCELLI - tel: 05 61 28 54 44
INRA, Laboratoire de Génétique Cellulaire
Chemin de Borde Rouge - Auzeville -BP27
31326 Castanet Tolosan
**************************************************