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

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. :(

Do you know the trigger can apply in inherit table ? If so, then we can
using OOP to resolve this problem (well, reduce our work.) :Q

Many thanks.

"ljb" <lbayuk@mindspring.com> wrote in message
news:9h8nk4$2uf3$1@news.tht.net...

Lincy.Lin@LinuxInBox.Com wrote:

HI !

Does anyone know what's my problem and how to resolve it ? Thanks.
...

Create Table DepartmentInfo
(
...
Department Char(40) Default ''
);
...
Create Table EmployeeInfo
(
...
Department Char(40) References
DepartmentInfo (Department)
...
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.

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 ?

without to using trigger (if possible).

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.

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Wednesday, July 04, 2001 8:17 AM
To: Lincy
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] 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.

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.

On Thu, Jul 05, 2001 at 11:16:50AM -0400, some SMTP stream spewed forth:

Hi,

I plan to design DB through pgaccess, but later I will need to
distribute a DB creation script so is there any command in pgsql to
produce the DB creation script ?

`man pg_dump`
pg_dump -s

gh

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

Hi,

I plan to design DB through pgaccess, but later I will need to
distribute a DB creation script so is there any command in pgsql to
produce the DB creation script ?

Thanks

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

On Thu, 5 Jul 2001, eddie iannuccelli wrote:

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 ?

"If a <column constraint definition> is specified that contains a
<references specification>, then it is equivalent to a <table constraint
definition> that contains the followin g<table constraint>:
CND FOREIGN KEY (C) <references specification> CA"

The only major difference that you generally have to worry about is
that table FOREIGN KEY constraints allow you to have multiple column
key references. It's similar to the difference between the column
and table level PRIMARY KEY specifications.

Generally I personally use the column level one for single column
keys and only use the table one for multiple column keys although that's
mostly a matter of style. I'm sure there are people that use the
table one for everything.

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

-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Thursday, July 05, 2001 3:39 AM
To: Lincy Lin
Cc: pgsql-general@postgresql.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.