0

Suppose I have 2 tables, Employee and Sale.

Employee table has EMP_ID int as PK, and ACTIVE_STATUS bit (0 for inactive and 1 for active). Sale table has SALE_ID as PK, EMP_ID as FK referencing Employee.EMP_ID and DATE_OF_SALE

Now, I want a constraint that checks if the EMP_ID I'm trying to insert into Sale has a value of 1 for the ACTIVE column in the Employee table, because I don't want to register a sale that is being attempted by an Inactive user.

How would I go about that? I tried CONSTRAINT CHECK_IF_ACTIVE CHECK(Employee.ACTIVE = 1) but it's not a valid statement.

  • 2
    Quite simply - don't. It is a laudable goal but don't try to make your schema "perfect". Problems arise all the time that require DML that does not follow the typical pattern. Leave this logic in your sale entry process. If you really think this needed, then you must use a UDF to accomplish it - simply web searching will find examples. – SMor Aug 13 '20 at 19:03
  • see https://stackoverflow.com/questions/13000698/sub-queries-in-check-constraint, a trigger seems to be the best thing to do – Turo Aug 13 '20 at 19:07

1 Answers1

4

You can do what you are specifically asking for using foreign keys and computed columns. First, define a redundant unique constraint in employees:

alter table employees add constraint unq_employees_empid_active_status on (empid, active_status);

Then, define a computed column in sales. Alas, this needs to be persisted, I think:

alter table sales add active_status as (convert(bit, 1)) persisted;

Then, define the foreign key constraint using both:

alter table sales add foreign key fk_sales_employees_active
    foreign key (empid, active_status) references employees(empid, active_status);

Voila! The employee id can only reference active employees.

Now, you will have a problem with this -- be careful what you ask for. It is not really what you want. This enforces the constraint over all time. So, you won't be able to change the status on employees who have sales. That suggests that you need an insert trigger instead -- or a user defined function and check constraint:

create function is_employee_active (
    @empid int
) returns bit as
begin
    return (select active_status from employees e where e.empid = @empid);
end;

alter table sales add constraint chk_sales_employee_active
    check (is_employee_active(empid) = convert(bit, 1));

Voila! This only does the check on insertion or updates. Note that once an employee is not active, you won't be able to update the row either.

You'll notice that I usually name my tables in the plural, because they contain lots of examples of an entity. My fingers just add the "s" when I'm thinking about tables.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • This may incur a high performance penalty. In my case this is prohibitive; 50k rows are checked in function, and over three fields, so it can process 10 per second, which means 75 minutes for every update of all rows, which occur often, so it is a no go. However, I see my model can reduce three fields to one if put into their own table, and their own unique index, which may go much faster and remove the need for uniqueness across different fields. However, it is a deep change. So.. :-/ – Thor Hovden Aug 15 '23 at 11:04