Geek asks and answers

How to force use constraint in sql?

I have a question related to enforcing my table. I have a table called workon and a table called staff, each employee has a specific title (supervisor, authorizer, manager, ...). I need to make sure that the supervisor and authorizer cannot be the same personnel on the desktop. The power between them is a lot for many. I am not sure how to do this. Could you advise me to solve this problem?

thank

+3
source share
2 answers

Consider the following query

SELECT AssignmentNo, StaffNo
  FROM worksOnStaff
 WHERE StaffType = 'supervisor'
INTERSECT 
SELECT AssignmentNo, StaffNo
  FROM worksOnStaff
 WHERE StaffType = 'authorizer'

When the data in the table meets your restriction, the above query will be an empty set i.e.

CHECK ( NOT EXISTS ( SELECT AssignmentNo, StaffNo
                       FROM worksOnStaff
                      WHERE StaffType = 'supervisor'
                     INTERSECT 
                     SELECT AssignmentNo, StaffNo
                       FROM worksOnStaff
                      WHERE StaffType = 'authorizer' ) );

, SQL CHECK.

, . ( ), , . , , .

, :

, Toon Koppelaars

, , ... , ;... , ... [] ... , , , ...

EM6: ---- +

  • .
  • .
  • (TE), , , .
  • , TE , .
  • (DI).

, ​​ Oracle, SQL.

0

:

CREATE TABLE workson
(
    rowId int NOT NULL,
    supervisorId int NOT NULL,
    authorizerId int NOT NULL,
    -- ...
    CONSTRAINT chk_Uniqueness CHECK (supervisorId != authorizerId)
)
0

More articles:


All Articles