CODEWITHSUNDEEP
databasepostgresqlentity-relationship
Daniel López
Daniel López

Reputation: 35

How can I solve this ER diagram for a database?

I have question with a ER diagram I'm creating for a database, I basically have 2 tables, one is called 'Employee' and the other is called 'Store'. Each store has certain number of employees, I need to know in what store the employee is located. The thing is, A store is required to have a boss/manager who has to be one of the employees. The picture below shows how I made it. However when I create these tables on PostgreSQL I get an error because I'm referencing a table who has not been created yet.

enter image description here

This is how I'm trying to create the two tables, how can I solve this? Any ideas?

create table Store(
store_id serial primary key,
storeName varchar(20),
employee_id int,
foreign key (employee_id) references Employee(employee_id)
);


create table Employee(
employee_id serial primary key,
firstname varchar(50),
lastname varchar(50),
address varchar(50)
email varchar(100),
store_id int,
foreign key (store_id) references Store(store_id)
);

Error:

SQL Error [42P01]: ERROR: relation "employee" does not exist

Upvotes: 0

Views: 272

Answers (1)

zolamk
zolamk

Reputation: 6357

All you would have to do is create the foreign key constraints after the tables have been created like this

create table Store(
  store_id serial primary key,
  storeName varchar(20),
  employee_id int
);


create table Employee(
  employee_id serial primary key,
  firstname varchar(50),
  lastname varchar(50),
  address varchar(50),
  email varchar(100),
  store_id int
);


alter table Store add constraint fk_store_employee_id foreign key (employee_id) references Employee(employee_id) DEFERRABLE INITIALLY DEFERRED;;


alter table Employee add constraint fk_employee_store_id foreign key (store_id) references Store(store_id) DEFERRABLE INITIALLY DEFERRED;;

the DEFERRABLE INITIALLY DEFERRED makes it that the constraint is checked at the end of the transaction otherwise you would get a foreign key constraint violation since the employee wouldn't exist when you create the store and the store wouldn't exist when you create the employee.

here is a working example https://www.db-fiddle.com/f/71b3KhwDMSgKmsWsbAeUTR/1

but even with the DEFERRABLE INITIALLY DEFERRED how would you know what id an employee and store is going to get?

i think you might be better off using a third table to store store managers like this

create table Store(
  store_id serial primary key,
  storeName varchar(20)
);


create table Employee(
  employee_id serial primary key,
  firstname varchar(50),
  lastname varchar(50),
  address varchar(50),
  email varchar(100),
  store_id int,
  foreign key(store_id) references Store(store_id)
);

create table StoreManager (
  store_id int,
  employee_id int,
  primary key(store_id, employee_id),
  foreign key(store_id) references Store(store_id),
  foreign key(employee_id) references Employee(employee_id)
);

this you can first create the store then the employee and then you can pair the two in the StoreManager table

here is a working example https://www.db-fiddle.com/f/71b3KhwDMSgKmsWsbAeUTR/2

Upvotes: 2

Related Questions