A column constraint that uses a custom function (udf)

Question

I have 2 tables - EmpDetailsandChangeLog

EmpDetailsstores information about employees - has ID, Nameetc.

ChangeLogIt is used to register changes in information about employees - it has ID, DateOfChange, ChangeDescription, etc.

I wanted to make sure that ChangeLog.IDis the value contained in the column EmpDetails.ID.

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

My question is: if a particular row IDis deleted from EmpDetails, will there be an error if IDthere are rows for this ChangeLog?

This does not seem to be so ... And I do not understand why.

So how do I get this functionality? One way I can imagine is to create a trigger for delete operations on EmpDetails..

Any other solution to this problem?

EDIT - I tried to specify a foreign key relationship. But the identifier in ChangeLog is not the key, since the ChangeLog table can contain several records for one ID (I mean that employees can change their data more than once, so there will be more than 1 record for the same identifier in ChangeLog). Do I have to indicate a relationship with a foreign key even then?

Answer 1

ChangeLog, EmpDetails, EmpDetails . SQL- , , , .

, EmpDetails , (a.k.a. )?

Answer 2

, , - . :

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

SQL Server , UDF. ChangeLog , EmpDetails EmpDetails, ChangeLog.