How do I create and insert rows to the following table schema in PostgreSQL:
Table: employee
emp_id, emp_name, emp_dept, emp_manager
Table: department
dept_id, dept_name, dept_manager
emp_manager is a foreign key to employee(emp_id)
emp_dept is a foreign key to department(dept_id)
dept_manager is a foreign key to employee(emp_id)
It can work like this:
CREATE TABLE employee (
emp_id int PRIMARY KEY
, emp_dept int NOT NULL
, emp_manager int
, emp_name text NOT NULL
, CONSTRAINT fk_emp_manager FOREIGN KEY (emp_manager) REFERENCES employee(emp_id)
, UNIQUE (emp_dept, emp_id) -- needed for FK fk_dept_manager
);
CREATE TABLE department (
dept_id int PRIMARY KEY
, dept_manager int
, dept_name text NOT NULL
, CONSTRAINT fk_dept_manager FOREIGN KEY (dept_id, dept_manager) REFERENCES employee(emp_dept, emp_id)
);
ALTER TABLE employee
ADD CONSTRAINT fk_emp_dept
FOREIGN KEY (emp_dept) REFERENCES department(dept_id);
Note how I change fk_dept_manager into a multicolumn FK reference to only allow employees of the same department to be department manager. Assuming you want that.
You might also want a CHECK constraint in table employee to disallow employees from being their own manager:
CHECK (emp_manager <> emp_id)
INSERT?As usual. To overcome mutual dependencies, either make FK constraints DEFERRABLE and run multiple commands in a single transaction (more expensive) or use a single command with one or more CTEs.
Example: to insert a new department and a new employee as its manager at once:
WITH ins_dept AS (
INSERT INTO department
(dept_manager , dept_name)
VALUES (nextval(pg_get_serial_sequence('employee', 'emp_id')), 'Sales')
RETURNING *
)
INSERT INTO employee
(emp_id , emp_dept, emp_manager, emp_name)
SELECT dept_manager, dept_id , NULL, 'Bob'
FROM ins_dept;
db<>fiddle here
Further reading: