Re: Re: 7.1.2 ERROR: UNIQUE constraint matching given keys for referenced table ...... - Mailing list pgsql-general

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
**************************************************