Unable to Create Sql Foreign key

Question

0.00/5 (No votes)

See more:

I have created 1 table
name as ItemMaster their I have created 2 Primary Keys
in Sql2008 (itm_grp_id and item_id)
n Second time I have Created another table Lets say tblOpeningClosing
in that their is field item_id
I Want To Add One Foreign Key on this table

SQL

alter table dbo.tblClosingStock
add Foreign Key (item_code)
References dbo.tblItemMaster(item_code)

Its not working

SQL

There are no primary or candidate keys in the referenced table 'dbo.tblItemMaster' that match the referencing column list in the foreign key 'FK__tblClosin__item___531856C7'.

Posted 26-Nov-12 21:18pm

Member-515487

Add a Solution

Comments

SoumenBanerjee 27-Nov-12 3:56am

please check is there any column of name "item_code" in dbo.tblItemMaster which has been seted as primary key?

Member-515487 27-Nov-12 23:19pm

i have assign 2 primary keys in tblItemMaster as Item_grp_code and item_code

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patel Shailendra · Accepted Answer · 2012-11-26T22:54:00

Solution 1

SQL

alter table dbo.tblClosingStock
add Foreign Key (item_code)
References dbo.tblItemMaster(item_code)

In tblItamMaster table you have assign primary key to item_code.
Check that.!

Posted 26-Nov-12 22:54pm

Patel Shailendra

Comments

Member-515487 27-Nov-12 23:19pm

i have assign 2 primary keys in tblItemMaster as Item_grp_code and item_code

Patel Shailendra 27-Nov-12 23:32pm

if you can show me your table script then i will help you much better way. if possible then only. because normally we assign primary key to one column of a table.

Member-515487 27-Nov-12 23:45pm

CREATE TABLE [dbo].[tblItemMaster](
[item_grp_id] [numeric](3, 0) NOT NULL,
[item_code] [numeric](6, 0) NOT NULL,
[item_desc] [varchar](50) NULL,
[UOM] [varchar](3) NULL,
[MOQ] [numeric](9, 3) NULL,
[yield_percentage] [numeric](6, 2) NULL,
[item_spec] [varchar](200) NULL,
[addusername] [varchar](15) NULL,
[adddate_time] [datetime] NULL,
[modusername] [varchar](15) NULL,
[moddate_time] [datetime] NULL,
CONSTRAINT [PK_tblItemMaster_1] PRIMARY KEY CLUSTERED
(
[item_code] ASC,[item_grp_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[tblClosingStock](
[TRANTYPE] [varchar](9) NULL,
[item_code] [numeric](6, 0) NOT NULL,
[batch_no] [varchar](15) NULL,
[opening_qty] [numeric](9, 3) NULL,
[receipt_qty] [numeric](9, 3) NULL,
[issue_qty] [numeric](9, 3) NULL,
[closing_qty] [numeric](9, 3) NULL,
[last_grn_date] [date] NULL,
[last_iss_date] [date] NULL,
[addusername] [varchar](15) NULL,
[adddate] [datetime] NULL,
[modusername] [varchar](15) NULL,
[moddate] [datetime] NULL,)

Patel Shailendra 27-Nov-12 23:59pm

Use item_code as int because numeric represent decimal values and use identity for increment.

e.g

Item_code int identity(3,0) not null

and create different table for group id because group id is group of item_code

eg
suppose u have Fruits is a group in that Apple have different item code and Banana have different item code

Member-515487 28-Nov-12 0:39am

k it will work thnks
hv u done ssrs client to remote
http://www.codeproject.com/Questions/498975/UnableplustoplusViewplusRemoteplusPCplusReport

Patel Shailendra 28-Nov-12 0:51am

till the time no

when i will get sufficient time then i will do.