Sql Table.. How to do this?

Question

0.00/5 (No votes)

See more:

SQL

Table employee
{
employeename  //employeename is unique
}

Table job
{
jobid,job,assignedPerson,status
}

assignedPerson is a foreign key that references to employeename(employee)<br>
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..

SQL

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 empolyee from 'employee' table and will not be able to know which person was assigned the job..<br>
Please help.. Thank You! :)

Posted 21-Aug-13 7:59am

Ashish sharma

Add a Solution

Comments

ZurdoDev 21-Aug-13 14:03pm

You add a flag to employe to indicate deleted or not so that you don't orphan your records.

Sergey Alexandrovich Kryukov 21-Aug-13 14:27pm

The question is: do you want to know who was assigned or not?
—SA

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

thatraja · Answer 1 · 2013-08-21T08:19:00

Don't delete record from employee table. Update Flag on particular employee.

SQL

DELETE FROM Employee WHERE Employee_ID='EMP001'

Employee
-----------
Employee_ID
Employee_Name
Active

Employee_Job
-----------
Assigned_Person
Job_ID
Job_Name
Status

When you want to delete an employee from table Employee, just update status for Active column for that particular employee.

SQL

UPDATE Employee SET Active='N' WHERE Employee_ID='EMP001'

If you want show only Active employees then use the below query(it won't show deleted employees)

SQL

SELECT * FROM Employee WHERE Active='Y'

OriginalGriff · Answer 2 · 2013-08-21T08:22:00

You can't. That's the whole idea of a foreign key relationship: it enforces database integrity by not allowing "orphaned" references.

Besides, you may well want to know in six months time which projects "Old Joe" worked on - because the ones you have looked at so far all have the same fault, perhaps. And what if a new employee has the same name as the guy who just left? The system would automatically assign all "Old Joe" projects to him if you reuse the name.

Instead, have an "employee status" column (probationary, freelance, permanent, deceased, departed would be a good set of values) and change the "Old Joe" status instead of deleting him - you want want to contact him to do freelance work on an old project of his, for example.

Sql Table.. How to do this?

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0