0

I have a table:

CREATE DATABASE IF NOT EXISTS test;
USE test;

CREATE TABLE IF NOT EXISTS emp_list(
id INT UNSIGNED NOT NULL AUTO_INCREMENT primary key,
first_name VARCHAR(30) NOT NULL,
last_name VARCHAR(30) NOT NULL,
job_position  VARCHAR(20) NOT NULL,
salary decimal(15,2) UNSIGNED,
);

INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ivan', 'Noscov', 'Director', 69000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Igor', 'Malov', 'Accountant', 54000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ira', 'Solnceva', 'Salesperson', 27000);
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Ivan', 'Noscov', 'Security guard', 15000);
UPDATE employees.emp_list SET last_name = 'Varnaev' WHERE id=4; 
INSERT INTO emp_list ( id, first_name, last_name, job_position, salary) VALUES ( null, 'Natasha', 'Bolshova', 'Salesperson', 27000);

I need update the database schematic so that it is possible to store the information about subordinates. No restrictions: each employee can have several subordinates (or not have them) and may have more than one boss (or no boss at all). Request a sample of all of a certain employee's subordinates.

Need to do this by adding a new column and need to use a foreign key to create relationships

1

You cannot do this with one additional column You need to create a new table for relations between employees.

CREATE TABLE IF NOT EXISTS emp_relations(
emp_id1 INT UNSIGNED NOT NULL,
emp_id2 INT UNSIGNED NOT NULL,

FOREIGN KEY (emp_id1 )
      REFERENCES emp_list(id),
FOREIGN KEY (emp_id2 )
      REFERENCES emp_list(id),

UNIQUE KEY(emp_id1 ,emp_id2)  
 );

Link both columns to emp_list.id. Make emp_id1 and emp_id2 together unique.

This way you will have hierarchy and many to many relations.

Mysql - Foreign keys

Mysql - Unique keys

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.