CODEWITHSUNDEEP
sqloracle-databaseforeign-keys
sania247
sania247

Reputation: 33

how to connect 2 tables with more than one relationship

I have 2 tables. One is Employee while the other is a department. So the first relationship is a one-to-many relationship where 1 employee can work in one department but one department can have many employees. So for this, we put the foreign key in the employee table.

The second relationship is a 1 to many relationship as well, although it is optional. It is a manages relationship. So one employee(optional since not every employee does this) can manage one or more department but one department is managed by one employee. Hence the foreign key is in the department.

create table dept(
    departmentName varchar2(10),
    mgrId integer,
    primary key(departmentName),
    foreign key mgrId references employee(empId)
);

create table employee(
    empId integer;
    empName varchar2(100),
    departmentName varchar2(10),
    primary key(empId),
    foreign key(departmentName) references dept(departmentName));

So this is technically how I want it but I know I will get an error because one of the tables I'm referencing to hasn't even been created yet. So should I just keep one relationship that is not the optional one? Or should I make a separate table for managers?

Upvotes: 1

Views: 171

Answers (2)

GMB
GMB

Reputation: 222672

You could first create both tables, then add constraints.

However the problem with this design is that the interdependency will cause issues not only when creating the tables, but also when inserting or updating data. It is always possible to temporarily disable a constraint when doing an update or insert, but it gets tedious when it needs to be repeated too often.

To avoid this, you can, as suggested, create a separate table to handle the manager relationship.

Note

It is even possible to also store the employee-department in another separate table instead of as a foreign key in employee. While this would provide a consistent solution for both relations, it may not be a good design choice : as commented by @APC, it is tricky to enforce a rule that a table must have a child, hence the number of such tables should be kept to the minimum.

Upvotes: 1

James
James

Reputation: 3015

You can add the foreign keys after creating the tables to skip the error

Upvotes: 0

Related Questions