Click here to Skip to main content
11,477,625 members (74,521 online)
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 22: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 Sergey Alexandrovich Kryukov 274
1 Richard Deeming 245
2 _duDE_ 165
3 Tomas Takac 150
4 Sascha Lefèvre 145
0 Sergey Alexandrovich Kryukov 7,675
1 OriginalGriff 7,256
2 Sascha Lefèvre 3,034
3 Maciej Los 2,491
4 Richard Deeming 2,305


Advertise | Privacy | Mobile
Web02 | 2.8.150520.1 | Last Updated 27 Nov 2012
Copyright © CodeProject, 1999-2015
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