column constraint that uses a user-defined function (udf)

I have 2 tables - EmpDetails & ChangeLog

EmpDetails stores details of employees - it has ID, Name etc.

ChangeLog is used to log changes to employee details - it has ID, DateOfChange, ChangeDescriptio开发者_如何转开发n, etc.

I wanted to make sure that ChangeLog.ID is a value contained in EmpDetails.ID column.

So, I put a CHECK constraint using a user-defined function for ChangeLog.ID column in (the UDF checks if ID exists in EmpDetails.ID or not)

My question is - if a particular ID's row is deleted from EmpDetails, will an error be raised if there are rows for that ID in ChangeLog ?

This does not seem to be the case... And I don't understand why.

So, how would I get such a functionality ? One way I can think of is to create a trigger for delete operations on EmpDetails..

Any other solution to the above problem ?

EDIT - I tried to specify a Foreign Key relationship. But ID in ChangeLog is not a key as the ChangeLog table can contain multiple records for the same ID (I mean , employees can change their details more than once, hence there will be more than 1 record for the same ID in ChangeLog). Should I be able to specify a Foreign Key relationship even in that case ?

What you are describing is a foreign key relationship. To enforce it:

ALTER TABLE ChangeLog
    ADD CONSTRAINT FK_EmpDetailsId FOREIGN KEY (ID)
    REFERENCES EmpDetails (ID);

SQL Server will then maintain the relationship for you, without the need for your UDF. Inserts to ChangeLog will fail if the corresponding row in EmpDetails does not exist and deletes from EmpDetails will fail if there is a matching row in ChangeLog.

The check constraint in on the ChangeLog table, not on the EmpDetails, so when you modify EmpDetails, it simply does not get checked. SQL server is not smart enough to figure out that you would want the check to run when some other table is changed.

But, if you just want to ensure that there is a row in the EmpDetails table, why not use a simple referential integrity rule (a.k.a. a foreign key)?