Foreign Key Constarint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Foreign Key Constarint

Author

Topic

Dipti
Starting Member

6 Posts

Posted - 2009-12-17 : 00:54:05

I have two tables

Employee(Emp_No,Emp_Nmae,Dept_No)

here dept_No referencing department table

Depatment(Dept_No,Dept_Name,Dept_Head)

Here Dept_Head referencing Emp_No in Employee table

So how to implemnet this concept

Plz Reply

Urgent

Dipti Sanghvi

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-17 : 00:57:18

what is the issue?

u have to create foreign key constraint b/w them?

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Dipti
Starting Member

6 Posts

Posted - 2009-12-17 : 01:00:58

Issue is which table is to be created first... employee or depatment becos depatment is referencing employee and employee is referencing depatment.

So how to solve this deadlock situation plz help

Dipti Sanghvi

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-17 : 01:20:39

according u r situation,

the FK should be created in employee table for Dept_No column.

though u wan to reference this column with column in department table.

u can acheive it in two ways.

1. create department table first
2. then create employee table like this:

cerate table employee
(
emp_no datatype keyinfo,
emp_name datatype,
dept_no datatype refernces Depatment(Dept_No)
)

2. if u want to create relationship b/w tables those are already exists,do it in this way:

ALTER TABLE [dbo].[Employee] ADD CONSTRAINT [FK_1] FOREIGN KEY
(
[Dept_No]
) REFERENCES [department] (
[Dept_no]
)
GO

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Dipti
Starting Member

6 Posts

Posted - 2009-12-17 : 01:28:24

Thanx a lot but while inserting values it wl give an error in which table Values should be inserted first Employee or department.
Plz help.
Is it really possible to implement this concept.

Dipti Sanghvi

rammohan
Posting Yak Master

212 Posts

Posted - 2009-12-17 : 01:30:35

insert into department table first then into employees.

y beause being a Foreign key the value eneterd into dept_no column in employees should occur in department table except null.

One can never consent to creep,when one feels an impulse to soar
RAMMOHAN

Dipti
Starting Member

6 Posts

Posted - 2009-12-17 : 01:39:46

Thanx for replying but issue is not with dept_no , it is with dept_Head in departemnt table.dept_Head is an employee in an employee table so how to insert value for depat_head in department table.

Thanx

Dipti Sanghvi

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources