November 29, 2007 at 7:19 am
Hi everybody,
I am getting an error when creating Foreign key relationship
Error is :The columns do not match an existing primary key or unique constraint
I am creating FK relationship to two columns from child table to master table . Master table emp chile table emp_dtl. FK relationship columns are empno and empname.
Thanks in Advance
the structure is like this
****************************************
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [test].[emp](
[empno] [int] NOT NULL,
[empname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[emp_dob] [datetime] NULL,
[emp_city] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
CONSTRAINT [PK_emp] PRIMARY KEY CLUSTERED
(
[empno] ASC,
[empname] ASC,
[emp_city] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [test].[emp_dtl](
[empno] [int] NOT NULL,
[empname] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[empdept] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[empdesig] [varchar](50) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[empbasic] [numeric](18, 2) NULL,
[empnet] [numeric](18, 2) NULL,
CONSTRAINT [PK_emp_dtl] PRIMARY KEY CLUSTERED
(
[empno] ASC,
[empname] ASC,
[empdept] ASC,
[empdesig] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
USE test
GO
****************************************************
Ramaa
November 29, 2007 at 7:25 am
You need to create a unique index or a primary key on the referenced table. In your case you can do this with:
ALTER TABLE [test].[emp]
ADD CONSTRAINT [PK_Emp]
PRIMARY KEY CLUSTERED (empno)
GO
(probably empno will identify the employee, so you do not need to use the empname. If this is not the case add the empname to the index like: PRIMARY KEY CLUSTERED (empno, empname)
If the empname is not needed in order to identify a row, your primary key should only reference the empno.
Regards,
Andras
November 29, 2007 at 7:38 am
Hi Andras,
Thank you for your response.
Primary key is already defined on the table.
My requirement is FK with empno and empname with emp.
Ramaa
November 29, 2007 at 7:45 am
Rama (11/29/2007)
Hi Andras,Thank you for your response.
Primary key is already defined on the table.
My requirement is FK with empno and empname with emp.
You can add a unique index in this case in the form of:
CREATE UNIQUE NONCLUSTERED INDEX [IX_emp_empno_empname]
ON [test].[emp] (empno,empname)
GO
This will make your foreign key work, but its usefulness is very much questionable. Referencing the primary key is sufficient. It should be unique on its own anyway, so I'm wondering why you are trying to add an extra column. It feels redundant. Would you share with us the problem you are trying to solve this way?
Regards,
Andras
December 3, 2007 at 6:10 am
Hi Andras,
Pls Execuse me for giving late reply. I was away from my office.
Even i am setting for single column as you said for foreign key references i am getting error. Insufficient columns like that....
Pls clarify me
Thank you for your replies....
Ramaa
December 3, 2007 at 6:50 am
Rama (12/3/2007)
Hi Andras,Pls Execuse me for giving late reply. I was away from my office.
Even i am setting for single column as you said for foreign key references i am getting error. Insufficient columns like that....
Pls clarify me
Thank you for your replies....
Hi Rama,
I've just noticed that your primary key on emp contains three columns (empno, empname, emp_city) (I had the assumption that the key column is only empno)
If you want a foreign key to this table, you can either use these three columns, or create a unique index on the columns you want to reference.
In this case, since you would like to reference only two columns (empno and empname) you may want to create a unique index on these two columns.
(see previous post entry)
Now, if you can create this unique index, this would allow you to have the foreign key. However, at the same time this would mean that the empno and empname columns are unique,
so they would identify the rows in your emp table. This means that your primary key column is not minimal, and you should redesign your table (you can reduce the size of your primary key).
If you cannot create this index (because the (empno and empname) columns together are not unique, then you cannot really create a foreign key this direction to these two columns, i.e. referencing the
emp table. (a pair of empno and empname would possibly refer to several rows).
Regards,
Andras
December 3, 2007 at 7:12 am
The problem is you have a composite Primary key in the master table
( emp)--right ?
Emp table : Primary Key is
empno , empname , emp_city --> composite primary key
Again the detail table(emp_dtl) you have
empno , empname , empdept, empdesig --> composite primary key
I think you need to create a composite FK key from detail table to Master table
So first define a unique key constraint with empno, empname , then try to create a composite Foreign key constraint which will have refernce to
the composite unique key of the master table.
Please refer BOL for more info.
"More Green More Oxygen !! Plant a tree today"
December 6, 2007 at 10:05 pm
Hi Andras & Minaz,
Thank you for your replies. The problem solved.
Ramaa
Viewing 8 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply