What is the correct way to enforce unique "IS-A" relationship?

Question

Let's say we have a Person relation, and with that Employee which IS-A Person, Manager, which ISA Person as well.

What is the correct way to ensure that a Manager isn't an Employee and vice versa ?

I didn't know what to look for when asking the question, there most likely already exists an answer to that question.

Person (PK:personId, name, ...);
Manager(PK:#personId); -- A manager ISA person
Employee(PK:#personId); -- An employee ISA person

INSERT INTO Person (1,'Peter',...);
INSERT INTO Manager (1); -- OK
INSERT INTO Employee(1); -- Nope, error.

Person cannot be at the same time an employee and a manager.

Maybe with another example.

Shape (shapeId);
Rectangle(shapeId, width, length);
Circle(shapeId, radius);

Circle is a shape, Rectangle is a shape, how do I ensure Rectangle is not a Circle (and vice versa)?

Hence my question, What is the correct way to enforce unique "IS-A" relationship?

Answer 1

Relation Application that refer to the Person and Position relations should have unique personID attribute and should have the single attribute positionID that can have one and only one value at once.

Answer 2

Confusion

You have two separate questions here which prevent this from being answered

• You have one question about relationships (IS-A).
• You have one question about constraints "manager isn't an employee".

One of them is about modeling, the other integrity. That makes it difficult to answer

Modeling

Any time you see IS-A with a (1:1) in the scope of an RDBMS, you should think simple foreign keys.

CREATE TABLE person (
   person_id  int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   -- stuff
);

CREATE TABLE manager (
   manager_id  int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   person_id   int REFERENCES person,
   -- stuff
);

CREATE TABLE employee (
   manager_id  int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
   person_id   int REFERENCES person
   -- stuff
);

If you have multiple inheritance (M2M), you'd use a linking table.

Integrity

"Manager isn't an Employee and vice versa"

This can only be done if either

• The relationship is stored in one table
• The database implement CHECK constraints which support queries
• You use a TRIGGER

That's your only option. If the relationship is stored in one table.

CREATE TABLE relationships (
  id_person  REFERENCES person,
  id_manager REFERENCES manager,
  UNIQUE ( greatest(id_person,id_manager), least(id_person,id_manager) )
);

And that should work.

Other Issues

These very simple cases often face bigger problems in the real world, for instance n-hierarchy relationships. What if the manager has a manager. That process of a separate table for each can get really absurd fast.

You could just do this,

CREATE TABLE persons (
  id_person     int PRIMARY KEY GENERATED BY DEFAULT AS IDENTITY,
  id_superior   REFERENCES persons
  ; stuff
);

But in this case there is no easy way to ensure id_person and id_superior do not create cyclic relationships in the graph, so again you have to get more clever...

And that's why this kind of question requires more information.