Problem in the datafields...for f.k.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Problem in the datafields...for f.k.

Author

Topic

sheena
Starting Member

45 Posts

Posted - 2007-06-14 : 10:01:42

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 ----- (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 ----- (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.)

Table :5 Payment
Fields

payid ---> int (p.k)
purchaseid --> int (f.k. to purchaseorder)
paydate --> datetime

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...

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-14 : 10:48:03

Am I missing something, or is this as simple as needing an itemid to insert into table 2? What do you mean you can't insert into Table 1? If you don't have a itemid, then you can't make an insert into Table 2, period.

sheena
Starting Member

45 Posts

Posted - 2007-06-14 : 13:09:26

Hi rudesyle,
thanxs...

I don't understand what u explained....

well can u test my table 2 & 3 fields 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...

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2007-06-14 : 13:51:32

Why don't you give us actual CREATE TABLE statements so that we can test and see what is going on?

- Jeff
http://weblogs.sqlteam.com/JeffS

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-14 : 14:05:00

Value you tried to insert into fkey column should exist in corresponding pkey column.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-14 : 14:27:05

No offense, but you need to crack open books online and do some studying on primary and foreign keys.

In short, you need an item (itemid) in order to insert a purchase order. The itemid must exist in the item table.

sheena
Starting Member

45 Posts

Posted - 2007-06-14 : 14:36:10

well..rudesyle
itemid is already there with a primary key..See Table 1:Item.

The problem is with Table:2 & 3

Pls check my following CREATE
thanxs

sheena
Starting Member

45 Posts

Posted - 2007-06-14 : 14:41:53

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

CONSTRAINT [PK_purchaseorder] PRIMARY KEY CLUSTERED
(
[purchaseorderid] 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

sheena
Starting Member

45 Posts

Posted - 2007-06-14 : 14:50:06

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]

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-14 : 15:02:47

You have to add item into table 1 first before adding rows in table 2 for that item.

sheena
Starting Member

45 Posts

Posted - 2007-06-14 : 15:07:13

rmiao...

I already have inserted the values in item table 1,2,3,4...
I m trying to insert the same in other tables then to getting the error..

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-14 : 15:18:30

Did you try to insert duplicated purchaseid? Has additional unique index on table 2?

sheena
Starting Member

45 Posts

Posted - 2007-06-14 : 15:32:45

Ya..as per my reqt...i want to insert the duplicate purchaseid...

For purchaseid --> 1
There can be many itemid 1,2,3,4
purchaseid-->2
There can be itemid 2,4,5,6,7

For this reason only i made here
itemid as primary as well as foreign key...

Table 2: PurchaseOrder

Fields :

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

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-14 : 20:49:42

If pkey has two columns, corresponding fkey should have those columns as well.

sheena
Starting Member

45 Posts

Posted - 2007-06-15 : 01:30:23

ya..rmiao...i think now i cld explain u what i wanted..

Can u suggest me some changes regarding it what can i do so that it works fine...if possible for u then..I want the output as i mentioned above...

Do i have to include the itemid in
Table 4: ReceiptNote and
Table :5 Payment aswell where i have used purchaseid

What if i make in all the tables as
purchaseid a primary key as well as foreign key...then ??

Thanxs..

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2007-06-15 : 14:57:00

You can't put pkey and fkey on same column in the table.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-15 : 15:07:31

quote:
Originally posted by rmiao

You can't put pkey and fkey on same column in the table.

errrr, yes you can.

rudesyle
Posting Yak Master

110 Posts

Posted - 2007-06-15 : 15:09:01

quote:
Originally posted by sheena

well..rudesyle
itemid is already there with a primary key..See Table 1:Item.

The problem is with Table:2 & 3

Pls check my following CREATE
thanxs

Yes, I know, I am talking about table 2. In order to insert a record into Table 2, you must have a valid itemid from Table1.

sheena
Starting Member

45 Posts

Posted - 2007-06-16 : 01:00:48

rmiao....we can put the datafield as primary key and foreign key both together if we want to derive the value from someother table...

sheena
Starting Member

45 Posts

Posted - 2007-06-16 : 01:09:53

rudesyle...

There is not at all a difficulty to insert the fields of itemid in Table-1 as well as in Table-2.

TABLE :1 has itemid 1 to 100
TABLE :2 should have also have itemid from 1to100 'coz that items are only in the stock.

What i want i one customer can purchase many items if he goes for shopping but the payment bill is made is only on that is one purchaseid with mulitple itemids...

The difficulty comes in inserting the values of TABLE 3,4,5 where i m trying to use the single purchaseid...

The table payment and receiptnote should have only purchaseid..there is not at all the reqt of itemid overhere....
So,can u pls tell me how to overcome this problem...

Now,i knew that if there is combined primary key then we should use in the other table as foreign key having the same value fields...

But,in my case it's not the reqt...how to do it..for table-3,4,5??

Thanxs....all for ur answers..

Thanxs

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources