How to constrain a value being named equal to another value in the same table in PostgreSQL-CodePudding

I want to make a table called manages, where an employee manages another employee. The table has values Manager_id and Employee_id which both reference a value - Emp_id - in another table called employee. I want to set a constraint so that I can't have two instances in the table where x manages y, and y manages x, that would not make sense. Another property of this table is that managers can have multiple employees and employees can be managed by multiple managers. So, the UNIQUE keyword does not seem to be of much use for me in this context.

CREATE TABLE manages
(
    Manager_id  INTEGER REFERENCES employee,
    Employee_id INTEGER REFERENCES employee 
)

I'm aware of the NULL keyword but I only know how to combine that will NOT NULL.

CREATE TABLE manages
(
    Manager_id  INTEGER REFERENCES employee NOT <?>,
    Employee_id INTEGER REFERENCES employee NOT <?>
)

CodePudding user response：

Put the NOT NULL constraint after the data type. You should also make both columns the primary key:

CREATE TABLE manages
(
    manager_id  INTEGER NOT NULL REFERENCES employee,
    employee_id INTEGER NOT NULL REFERENCES employee, 
    primary key (manager_id, employee_id)
)

The primary key would make the columns NOT NULL as well, but I prefer to be explicit about this.

This still allows an employee to be managed by multiple manager and a manager to be manage multiple employees. What it disallows to insert the same combination of employee_id and manager_id twice.