Problem in creating foreign key when .....in sql server 2005...pls help.thanxs

sheenaa

Hello frdz,

I m working with SQL SERVER 2005.

My problem is with the creation of foreign key for some table thru which i m not able to insert the data.

This table are samples i have included only main/few datafields and type from the table :

Please test and rectify if any errors...and tell me what's problem.What changes can be made to perform insert..


Table 1:Item

Fields :

itemid --> int identity ----- (p.k.)
qty --> int

Table 2: PurchaseOrder


Fields :
purchaseid --> int identity ----- (p.k.) error
itemid --> int ---- (p.k.,f.k.) not able to make purchaseid f.k. in purchasereturn ?
orderdate --> datetime

Table 3: PurchaseReturn


Fields :

purchasereturnid --> int identity ----- (p.k.)
purchaseid --> int ----- (f.k.)?
itemid --> int ----- (f.k.)
returndate --> datetime

Table 4: ReceiptNote


Fields :

receiptid --> int identity ----- (p.k.)
purchaseid --> int ----- (p.k.)



Explanation :


Table 1:Item There are 100 records of items in table with all its basic details.

Table 2: PurchaseOrder The order placed by the customer can be as follows ....this is what i want to insert data into table.
Sample-1
orderdate --> June 14,2007

purchaseid --> 101
itemid --> 1,4,5,2,6,10

Sample-2
orderdate --> June 14,2007

purchaseid --> 102
itemid --> 1,6,10



There can be multiple items order placed for purchaseid 1.

Table 3: PurchaseReturn The purchase return of items will be as per purchase of items done.....this is what i want to insert data into table.
Sample-1
returndate --> June 20,2007

purchasereturnid --> 201
purchaseid --> 101
itemid --> 5,10

The items can be return as per purchaseid and items purchased.

Table 4: ReceiptNote The receiptnote contains the details of the purchase of items done and the payment made.


Hope to get a reply...
Thanxs in advance...

Jun 14 '07 #1

Purple

Hi sheenaa,

I have some questions based on your post:

Looking at:

[HTML]Sample-1
orderdate --> June 14,2007

purchaseid --> 101
itemid --> 1,4,5,2,6,10

Sample-2
orderdate --> June 14,2007

purchaseid --> 102
itemid --> 1,6,10[/HTML]

you have specified a value for purchaseid yet if your definition for table two you have specified it as identity

[HTML]Table 2: PurchaseOrder

Fields :
purchaseid --> int identity ----- (p.k.) error
itemid --> int ---- (p.k.,f.k.) not able to make purchaseid f.k. in purchasereturn ?
orderdate --> datetime[/HTML]

are you trying to insert a value into this field ? if you are then this is prob your issue. The identity field is automatically populated by MSSQL server.

If this isn't the case please post your sql code into the thread so we can take a look

Regards Purple

Jun 14 '07 #2

sheenaa

Thanxs Purple for ur reply...

No,this is not the case...

The Sample is the output.I know that identity column increases by itself.I m not inserting data.It's the dummy data as sample.

I think i m not able to explain u properly...

Ok,well can u test my table 2 & 3 in SQL SERVER 2005.There is problem in creating the table with foreign key...

The column in table PurchaseOrder do not match an existing primary key or
UNIQUE constraint.

So,only i explained with the sample output what i needed...

Is it b''coz
purchaseid --> int identity ----- (p.k.) error
itemid --> int ---- (p.k.,f.k.) not able to make purchaseid f.k. in purchasereturn ?

Thanxs..
Hope to get a solution now....

Jun 14 '07 #3

Purple

Hi Sheena,

I am currently loading SQL2005 on my Vista business partition - I will come back to you shortly..

Purple

Jun 14 '07 #4

sheenaa

Well thanxs...
I removed the identity column from table-2 & 3 as it does not allows to enter dupilcate values..
But still having the same problem..

Table 1:Item

Fields :

itemid --> int identity ----- (p.k.)
qty --> int

Table 2: PurchaseOrder


Fields :
purchaseid --> int ----- (p.k.)
itemid --> int ---- (p.k.and f.k. to itemmaster)
orderdate --> datetime

Table 3: PurchaseReturn

Fields :

purchasereturnid --> int ----- (p.k.)
purchaseid --> int ----- (f.k.) ?
itemid --> int ----- (f.k.)
returndate --> datetime

Table 4: ReceiptNote


Fields :

receiptid --> int identity ----- (p.k.)
purchaseid --> int ----- (p.k.)


I think i m understanding my problem now...but not able to solve it..
I m getting this error b'coz of this fields



Table 2: PurchaseOrder

Fields :
purchaseid --> int ----- (p.k.)
itemid --> int ---- (p.k.and f.k. to itemmaster)

Here i have made itemid as primary key and foreign key as i can enter duplicate values of items for the different purchase orders...

Now, comes the real problem

Table 3: PurchaseReturn

Fields :

purchasereturnid --> int ----- (p.k.)
//
THE PROBLEM IS OVER HERE
//
purchaseid --> int ----- (f.k. to purchaseorder with purchaseid & itemid) ??
itemid --> int ----- (f.k. to itemmaster ) ??


I m not able to insert this values.If i enter only one itemid then it's ok.But,multiple items are not allowed to return ..why ??
Sample-1
returndate --> June 20,2007

purchasereturnid --> 201
purchaseid --> 101
itemid --> 5,6,10,11

For,this table's purchaseid i made the F.K. with
purchaseorder as purchaseidand itemid
as i have in the table purchaseorder

U pls test on ur SQL SERVER 2005..
And tell me what changes can i do so that i can get the records which i had put on the top of the page as sample.
Pls insert the same dummy records...

CREATE TABLE [dbo].[purchaseorder](
[purchaseorder] [int] NOT NULL,
[itemid] [int] NOT NULL,
[orderqty] [int] NOT NULL,
[orderdt] [datetime] NOT NULL,

CONSTRAINT [PK_purchaseorder] PRIMARY KEY CLUSTERED
(
[poid] ASC,
[itemid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[purchaseorder] WITH CHECK ADD CONSTRAINT [FK_purchaseorder_item] FOREIGN KEY([itemid])
REFERENCES [dbo].[item] ([itemid])
GO
ALTER TABLE [dbo].[purchaseorder] CHECK CONSTRAINT [FK_purchaseorder_item]
GO


Thanxs...

Jun 14 '07 #5

sheenaa

CREATE TABLE [dbo].[purchasereturn](
[purchasereturnid ] [int] NOT NULL,
[purchaseorderid] [int] NOT NULL,
[itemid] [int] NOT NULL,
[purchasereturndate] [datetime] NOT NULL,
[prqty] [int] NOT NULL,

CONSTRAINT [PK_purchasereturn] PRIMARY KEY CLUSTERED
(
[purchasereturnid] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]


ALTER TABLE [dbo].[purchasereturn] WITH CHECK ADD CONSTRAINT [FK_purchasereturn_item] FOREIGN KEY([itemid])
REFERENCES [dbo].[item] ([itemid])
GO
ALTER TABLE [dbo].[purchasereturn] CHECK CONSTRAINT [FK_purchasereturn_itemmaster]
GO
ALTER TABLE [dbo].[purchasereturn] WITH CHECK ADD CONSTRAINT [FK_purchasereturn_purchase] FOREIGN KEY([purchaseorderid], [itemid])
REFERENCES [dbo].[purchaseorder] ([purchaseorderid], [itemid])
GO
ALTER TABLE [dbo].[purchasereturn] CHECK CONSTRAINT [FK_purchasereturn_purchase]

Jun 14 '07 #6

Purple

Hi,

have the SQL server 2005 install loaded and just running the service pack - can you post the SQL to create the tables you have and insert the sample data..

Regards Purple

Jun 14 '07 #7

sheenaa

Hi,

have the SQL server 2005 install loaded and just running the service pack - can you post the SQL to create the tables you have and insert the sample data..

Regards Purple

Ya...already put the create statements for two tables

As m not able insert the records in it...i have not put insert query.

Table :5 Payment
Fields

payid ---> int (p.k)
//
THE PROBLEM IS OVER HERE
//
purchaseid --> int (f.k. to purchaseorder)
paydate --> datetime

Thanxs...

Ok...No problem pls try to reply tomorrow if possible...then

Jun 14 '07 #8

Purple

Hi,

I am having some issues with SQL 2005 and my Vista partition - looks like the service pack hasn't gone on properly..

I am going to have to pick this up again tomorrow..

Regards Purple

Jun 14 '07 #9

sheenaa

Hi,

No reply after 2-4 days...can u help me out then pls answer...

Thanxs

waiting for some reply...which solves or gives some solution to my problem....

Jun 19 '07 #10