Re: Relating tables

bucaliman_at_gmail.com wrote:
> Hello database experts! Please could you help to relate these tables?
> One user can have many companies. Each company may have many employees.
>
> USERS
> idusers (PK)
>
> COMPANIES
> idcompanies (PK)
> users_idusers (FK)
>
> EMPLOYEES
> idemployees (PK)
> companies_idcompanies (FK)
>
> USERS and COMPANIES are linked.
> I did this to link COMPANIES and EMPLOYEES:
>
> ALTER TABLE `employees` ADD CONSTRAINT `employees_fk` FOREIGN KEY (
> `companies_idcompanies` ) REFERENCES `companies` ( `idcompanies` ) ON
> DELETE CASCADE ON UPDATE CASCADE
>
> I got this error: Cannot add or update a child row: a foreign key
> constraint fails.

This means that there exists a row in employees table with a company that does not exist in the company table. You can find out which row by:

select * from employees e where not exists (select 1 from companies c where e.companies_idcompanies = x.idcompanies)

I.e. a company must exist before it can have any emploeeys. Also concider changing the name of your attributes

USERS
user_id

COMPANIES
company_id
user_id

Doing so will simplify your work a lot

HTH
/Lennart Received on Sat Apr 08 2006 - 11:12:22 CEST