I have 3 tables:
table1:
Employee_class(PK),
Executive_lounge,
Executive_washroom,
Supply_closet,
Employee_lounge,
Server_room,
Server_closet
table2:
Emp_id(PK),
LName,
Years_of_service,
Emp_Class (FK) ref Employee_class in table1
table3:
Transaction_id (PK),
Emp_id (FK) ref Emp_id in table2,
Access_time,
Access_location,
Allowed
table1 is used to know if a certain class employee can access a particular location
table2 contains details of employees
table3 records every time an employee accesses a particular location. Now, access should be permitted only when the employee class of the employee is allowed to access that particular location.
So what I am trying to do is to insert into table3, but I wish to give constraints as well, i.e., I want to check Emp_Class of Emp_id from table2 and then further check from table1 if Employee_class has access to that particular location, only then allow insert in table3.
How can I do this? I was thinking about writing an insert query with a join on table3 and table2, with case but that is apparently not allowed in SQL. I am free to consider procedures and triggers but as I am new to advanced SQL, I am not sure how to do that.
Thanks. Also, I apologize if my question is framed poorly, I am new to this , please bear.
Best Answer
Since the LOCATIONS and employee CLASSES are quite important for your database, you should probably give them separate tables. Here some suggestions - which may serve as a starting point (including some test data, using Oracle 12c).
When it comes to permissions, things get a bit more complicated :-). Apparently, you want to be able to assign access permissions to GROUPS (classes of employees). However, in the "access log" (your table3), you are recording events that are associated with SINGLE users. Thus, (just as an idea) we could create a PERMISSIONS table, where we record all permissions (per employee). You see that any of the valid combinations of EMPLOYEEID and LOCATIONID can be recorded in the access log table (code below), whose foreign key constraint references the permissions table.
Testing
Our "access log" now contains:
Now - if you also want to record "access denied" events, and want to use GROUP/class level permissions, too, then we need triggers (frowned upon...) or procedures (the bee's knees...). The main reason for this being: CHECK constraints only work within a single table. Hence, we cannot check whether a particular employee belongs to a particular group AND whether this group is allowed to access a particular location (even in your original layout, the class data and employee data are located in 2 tables ...).
For recording all valid combinations of classes and locations, we should probably add another table (your original design will contain loads of NULLs in table1). Also, the accesslog gets an additional column, that allows us to record "access denied" events.
The procedure code should go into a package (which is omitted here, for clarity). The code given below is far from perfect, but it should give you something to work with - if you have done some PL/SQL before.
When testing the procedure, you may find that some calls will cause an ORA-02991. You can disable the accesslog's FK constraint in order to allow the logging of "access denied" events.
Sorry for the length of this answer! The gist is: do whatever you can with integrity constraints! Let the DDL code do the work. CHECK constraints do not work across tables. When you look at the (very crude) diagram, you will see that the LOCATIONS_CLASSES is not connected to the access log. The procedure "enforces a constraint" - but not really, as we want to record "access denied" as well.
If you want to use a procedure, you may not need the PERMISSIONS table. However, if you can get away WITHOUT writing a procedure, and only use integrity constraints, you'll get a more robust solution!