foreign key

Question

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 insertedit may have to changed wid updated department name

Akkiraju Ivaturi · 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 Ivaturi · Answer

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 kanani · Answer

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 kanani · Answer

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 Ivaturi · Answer

Your department table should be something as below: department ============ departmentid int identity(11) 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.