newbie help

[shaolinf]

Hi Guys,

If I have 2 tables named "Employee" and "department" and the EmployeeID from the Employee table is a foreign key in the the department table. It is a one-2-one relationship.

Now if the Primary Key EmployeeID in the employee table is changed or updated or removed, then what should I do with the EmployeeID foreign key ?

 

[elsenorjose]

It would also need to change. Depending on your DBMS, the way this is implemented will vary.

Post your question in the forum for the database you are using for the specific syntax.

 

[r937]

i think you have either your terminology backwards or your design is bad

if the department table contains an employee_id, then you can assign each department to only one employee

so if this is meant to be the relationship for the department manager, than it's probably okay, as each department might have only one manager, but in that case i would name the column deptmgr_id, not employee_id

but if this is meant to be the department-has-many-employees-in-it relationship, then what you should have is the dept_id as a column in the employee table, signifying that an employee can belong to only one department

if that's not true either, and an employee can belong to multiple departments, then you need a third table

regarding keys changing, do a search for ON UPDATE CASCADE

r937.com | rudy.ca

 

[SQLSister]

It is also best to design your system so that the primary key never needs to change. For instance we use an identity field for clientid through our system. The client name is stored in the client table and links to everything else through the numeric field. If the client company changes names, then all we have to do is update one field in one table. If you use the client name as the key field then not only do you have to update all references if it changes (which it will), but the joins are likely to be less efficient and likely more disk space will be used.

"NOTHING is more important in a database than integrity." ESquared