Recursive table SQL

Question

I´m new to SQL so I´m sorry if I make some simple mistakes. Having said that, I wanted to create a recursive table but I´m not sure how.

I have this table:

CREATE TABLE employee(
employee_id INT NOT NULL ,
Bdate DATETIME,
address VARCHAR(80),
PRIMARY KEY(supervisor_number)
);

And I have a 1:N cardinality in which 1 supervisor can supervise N supervisees. That´s why i need a recursive table, because an employee can either be a supervisor or a supervisee.

I don´t know how to create the recursive table so if anyone knows how I would be extremely grateful! If there´s anything that isn´t clear let me know.

Thanks!

And I have a 1:N cardinality in which 1 supervisor can supervise N supervisees. Does this means "1 supervisor can supervise from 0 to N supervisees" ? PS. Built-in data integrity system cannot check such constraint. External server-side checking (trigger) may solve. I wanted to create a recursive table This term not exists. You mean self-referenced table. — Akina
– Akina, Commented Apr 6, 2021 at 18:50
@Akina As I mentioned in the question 1 supervisor can supervise 1 to N supervisees. And thanks for the insight I will make to use that from now on — fiona
– fiona, Commented Apr 6, 2021 at 20:36

ktaria · Accepted Answer · 2021-04-06 18:01:17Z

1

create a table of relation , you can call it supervision;

it the new table , put 2 fields : supervisor and supervisee; these two fields are foreign keys to the table Employee ;

for example, employee number 1 is a supervisor for the employee 2 and 3, you insert two lines in the new table :

insert into supervision (supervisor, supervisee) values (1, 2), (1, 3);

answered Apr 6, 2021 at 18:01

ktaria

4617 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

fiona Over a year ago

Is there a way of doing it without creating a table of relation?

ktaria Over a year ago

you can put the supervisor number as you did in the first reation ;

Akina · Accepted Answer · 2021-04-07 06:26:50Z

Possible realization.

The table

CREATE TABLE employee(
employee_id INT NOT NULL ,
Bdate DATETIME,
address VARCHAR(80),
PRIMARY KEY(employee_id),
supervisor_id INT,
FOREIGN KEY (supervisor_id) REFERENCES employee(employee_id) 
        ON DELETE SET NULL ON UPDATE CASCADE );

The triggers which checks for subordinates amount (the value of 3 is hardcoded)

CREATE TRIGGER tr_bi_check_subordinates_amount
BEFORE INSERT
ON employee
FOR EACH ROW
BEGIN
IF 3 <= ( SELECT COUNT(*)
          FROM employee
          WHERE supervisor_id = NEW.supervisor_id ) THEN
    SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No available slots for specified supervisor';
END IF;
END

CREATE TRIGGER tr_bu_check_subordinates_amount
BEFORE UPDATE
ON employee
FOR EACH ROW
BEGIN
IF 3 <= ( SELECT COUNT(*)
          FROM employee
          WHERE supervisor_id = NEW.supervisor_id ) THEN
    SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = 'No available slots for specified supervisor';
END IF;
END

DEMO fiddle

Collectives™ on Stack Overflow

Recursive table SQL

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related