Sql表..怎么做？ [英] Sql Table.. How to do this?

问题描述

Table employee
{
employeename  //employeename is unique
}

Table job
{
jobid,job,assignedPerson,status
}

assignedPerson是一个引用employeename（员工）的外键< br>

我想知道如何删除员工记录而不必从工作表中删除他的记录，即我的意思是我想保留即使该人不再是雇员，也可以找到工作记录并查看该工作人员。

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..

Table Employee
    {
    employeename
    }

    Table jobemployee
    {
    jobid,assignedPerson
    }

    Table job
    {
    jobid,job,status
    }

即使我这样做，我也必须从jobemployee删除记录我想从'员工'表中删除empolyee的记录，并且无法知道哪个人被分配了这份工作。< br>

请帮忙..谢谢！ ：）

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! :)

推荐答案

不要从员工表中删除记录。更新特定员工的旗帜。

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

DELETE FROM Employee WHERE Employee_ID='EMP001'

员工

---------- -

Employee_ID

Employee_Name

有效



Employee_Job

-----------

Assigned_Person

Job_ID

Job_Name

状态



如果要从Employee表中删除员工，只需更新该特定员工的Active列状态。

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.

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)

SELECT * FROM Employee WHERE Active='Y'

你不能。这就是外键关系的全部概念：它通过不允许孤立引用来强制数据库完整性。



此外，你可能想在六个月内知道哪个项目老乔的工作 - 因为到目前为止你看过的那些都有同样的错误。如果一名新员工与刚刚离职的员工同名，该怎么办？如果您重复使用该名称，系统会自动将所有Old Joe项目分配给他。



相反，有一个员工身份专栏（试用，自由职业，永久，已故，离职将是一组很好的价值观）并改变老乔状态而不是删除他 - 例如，你想要联系他在他的旧项目上做自由职业。

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表..怎么做？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！