Thread: Re: 7.1.2 ERROR: UNIQUE constraint matching given keys for referenced table ......
Re: 7.1.2 ERROR: UNIQUE constraint matching given keys for referenced table ......
From
lbayuk@mindspring.com (ljb)
Date:
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 >> "departmentinfo" not found Make DepartmentInfo.Department a primary key. If not possible, make a unique constraint on it. If it isn't unique, you shouldn't be referencing it as a foreign key from EmployeeInfo.
Re: 7.1.2 ERROR: UNIQUE constraint matching given keys for referenced table ......
From
"Lincy"
Date:
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 > >> "departmentinfo" not found > > Make DepartmentInfo.Department a primary key. If not possible, make a > unique constraint on it. If it isn't unique, you shouldn't be referencing > it as a foreign key from EmployeeInfo.
Re: Re: 7.1.2 ERROR: UNIQUE constraint matching given keys for referenced table ......
From
Stephan Szabo
Date:
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.
RE: Re: 7.1.2 ERROR: UNIQUE constraint matching given keys for referenced table ......
From
"Lincy Lin"
Date:
Thanks your reply. Actully, We are using 4 fields (CompanySerialNo, BrahcnOffice, DepartmentCategory, Department) to check employee work forwhich company parimary. Well, we have many company here (In Taiwan) have multi-company name but in same office. Some employeework 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 in7.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.