I don't know if anyone of you have done this:



I'd like to have a lock in place that will keep an employee from being
deleted from the employee directory if their employee.id is in any tables
(loan table for example). The employee.id can have a FK in any number of
tables at anytime and I would like to not hard code this check. What I'd
like to do is write a query that will return all the tables in the database,
then query all columns to look for the employee.id that is about to be
deleted. If it exists, the employee cannot be deleted. Is this possible?
Anyone ever try it?

What do you meen by employee directory?
in case this is defined as FK, then SQL server automatically enforece the
integrity of data and will not let user delete from the employee table.

try this example

create table employee (eid int CONSTRAINT PK_emp_id PRIMARY KEY CLUSTERED,
ename varchar(50) )
create table loan (eid int REFERENCES employee(eid), loan_amt int)
go
insert into employee values (1,'Myname1')
insert into employee values (2,'Myname2')
insert into employee values (3,'Myname3')
insert into employee values (4,'Myname4')

insert into loan values (2,500)
insert into loan values (4,500)

below delete can't delete record from employee table as this record is
referenced into loan table
delete from employee where eid=2

However below one will be deleted as there is no reference
delete from employee where eid=3

HTH
Falik

[quoted text, click to view]