Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
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
alter table dbo.tblClosingStock
add Foreign Key (item_code)
References dbo.tblItemMaster(item_code)
Its not working
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
Comments
Soumen_Banerjee at 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 at 27-Nov-12 23:19pm
   
i have assign 2 primary keys in tblItemMaster as Item_grp_code and item_code

1 solution

Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

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.!
  Permalink  
Comments
Member-515487 at 27-Nov-12 23:19pm
   
i have assign 2 primary keys in tblItemMaster as Item_grp_code and item_code
shailendra194878 at 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 at 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,)
shailendra194878 at 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 at 28-Nov-12 0:39am
   
k it will work thnks
hv u done ssrs client to remote
http://www.codeproject.com/Questions/498975/UnableplustoplusViewplusRemoteplusPCplusReport
shailendra194878 at 28-Nov-12 0:51am
   
till the time no
 
when i will get sufficient time then i will do.

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

  Print Answers RSS
0 OriginalGriff 310
1 Sergey Alexandrovich Kryukov 201
2 PhilLenoir 164
3 Magic Wonder 162
4 Richard MacCutchan 160
0 Sergey Alexandrovich Kryukov 6,140
1 OriginalGriff 5,195
2 CPallini 2,473
3 Richard MacCutchan 1,607
4 Abhinav S 1,505


Advertise | Privacy | Mobile
Web01 | 2.8.140814.1 | Last Updated 27 Nov 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100