asked on

INSERT tatement conflicted with the FOREIGN KEY

I've been trying to find solutions for my problem but i could not find anywhere. I tried all resources.

I've two table in SQL Express Edition.

TABLE Employee
EmpID int identity(1000,1) primarykey not null
Name varchar (50)
Address1
Address2
City
State
Zip

TABLE Salary
SalID int identity(2000,1) primarykey not null
EmpID int
Name varchar (50)
Address1
Address2
City
State
Zip

and this two table has a foreignkey realation on EmpID but never worked. I'm really really tired finding solution.

Please help.

Guy Hengel [angelIII / a3]

>and this two table has a foreignkey realation on EmpID but never worked
can you explain that?

is the field EmpID not nullable on Salary table? if it allows NULL, then you can have null on it.
can you show the foreign key constraint sql you used to set it up?

ashwin197816

ASKER

hi Angellll.

Thank you for quick response.

Here are the tables.
CREATE TABLE [dbo].[EMPLOYEE](
      [EmpID] [int] IDENTITY(1000,1) NOT NULL,
      [InterchangeControlNo] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SenderID] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SenderID_Qlfr] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ReceiverID] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ReceiverID_Qlfr] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Version] [nvarchar](5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_EMPLOYEE] PRIMARY KEY CLUSTERED
(
      [EmpID] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

Second Table

CREATE TABLE [dbo].[SALARY](
      [GroupKey] [int] IDENTITY(2000,1) NOT NULL,
      [EmpID] [int] NOT NULL,
      [InterchangeControlNo] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [GroupNo] [nvarchar](9) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [Version] [nvarchar](12) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [FunctionalIdCode] [nvarchar](2) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [SenderDept] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
      [ReceiverDept] [nvarchar](15) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_SALARY] PRIMARY KEY CLUSTERED
(
      [GroupKey] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[SALARY] WITH CHECK ADD CONSTRAINT [SALARY_EZ_EMPLOYEE_FK1] FOREIGN KEY([EmpID])
REFERENCES [dbo].[EMPLOYEE] ([EmpID])
GO
ALTER TABLE [dbo].[EZ_SALARY] CHECK CONSTRAINT [SALARY_EZ_EMPLOYEE_FK1]

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial