foreign key

vikas kanani

14y1.3k.NET

suppose i have 1 table for department. and table for employee details.department is primary key for department table.if i can update a department from department table then watever records were inserted,it may have to changed wid updated department name

5 Replies

Know the answer? Post it — somebody with the same question will find it here.

Akkiraju Ivaturi14y ago
Accepted answer
Ok. Take in this way. There are two employees emp1 and emp2 working for department dept1. emp3 working for dept2.

Now department table looks like below:
deptid   name
1 dept1
2 dept 2

Employee details table:
empid   name        deptid
1         emp1        1
2         emp2        1
3         emp3        2

So, if you write a select statement joining two tables then your data looks like below:

emp1 dept1
emp2 dept1
emp3 dept2

Now you want to change the name of dept1 to mydept. So, you go to department table and modify the name of dept "dept1" to "mydept".

Now you run the select statement again and you see now:

emp1 mydept
emp2 mydept
emp3 dept2

So, if you are changing the name in the master table department, you do not need to change any other table values for the same. Still you get the changed department name when you write a select statement.

If this is not clear send me the data and what you want to change let me know so that I can explain you in more detail.
Akkiraju Ivaturi14y ago
I have attached db script for creating tables and also for querying both the tables for the data to be displayed in UI. If you have any questions, let me know.
vikas kanani14y ago
it worked.but problem is when i search record from employee details table.how can i get department name in textbox when i search record.
vikas kanani14y ago
but if i can take dept id is primary key then i can write 2 or 3 same name of department name. plz explain me in detail.
Akkiraju Ivaturi14y ago
Your department table should be something as below:
department
============
departmentid int identity(1,1) primary key
departmentname varchar(500)

Sample data:
===========
1 dept1
2 dept2

EmployeeDetails
===========
empid
deptid

Sample data:
1 1
2 1
3 2

Since departmentid is a int and is the primary key we always use that id for joining the tables not the departmentname. If you change department name, then you do not need to change the same in the employee details table.

Let me know if you have any questions.