|
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...
| |
Share:
Expert 256MB |
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
| | |
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....
| | Expert 256MB |
Hi Sheena,
I am currently loading SQL2005 on my Vista business partition - I will come back to you shortly..
Purple
| | |
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...
| | |
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]
| | Expert 256MB |
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
| | |
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
| | Expert 256MB |
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
| | |
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....
| | Post your reply Sign in to post your reply or Sign up for a free account.
Similar topics
2 posts
views
Thread by Kamlesh |
last post: by
|
3 posts
views
Thread by Jitender Singh Rawat |
last post: by
|
2 posts
views
Thread by Ian Davies |
last post: by
|
6 posts
views
Thread by Jeff North |
last post: by
|
8 posts
views
Thread by TGEAR |
last post: by
| |
1 post
views
Thread by apax999@gmail.com |
last post: by
| |
1 post
views
Thread by Zeljko Bilandzija |
last post: by
| | | | | | | | | | |