CODEWITHSUNDEEP
sqlforeign-keysrelation
ash
ash

Reputation: 156

Can a foreign key be optional

Table employee
{
employeename  //employeename is unique
}

Table job
{
jobid,job,assignedPerson,status
}

assignedPerson is a foreign key that references to employeename(employee)
What I want is to know how to delete an employee record without having to remove his record from the job table i.e. I mean I want to keep the job record and see which person was assigned the job even if that person is no longer an employee..

Table employee
{
employeename
}

Table jobemployee
{
jobid,assignedPerson
}

Table job
{
jobid,job,status
}

Even if I do this I will have to delete the record from jobemployee if I want to delete the record of employee from 'employee' table and will not be able to know which person was assigned the job..
Please help..

Upvotes: 0

Views: 383

Answers (1)

Andomar
Andomar

Reputation: 238078

The best option is to add a flag to the Employee table for deleted.

A workaround is to add a column assignedNonEmployee to the job table. Before you delete an employee, you set the assignedPerson column to null, and copy its value into the assignedNonEmployee column. Since the later column has no foreign key constraint, you are now allowed to delete the employee record.

Upvotes: 1

Related Questions