Hai,

For the intro : I'm using SQL Server 2000, and i use client side cursor, and i want to use resync command from ADO. The problem is that the resync needs PK from the table.

And here is my table:
1. Sales Order
2. Order

The Sales Order consist of:
1. Order No. (PK)
2. Date
3. Client
4. etc

The Order table consist of:
1. Order No. (FK)
2. No
3. Item
4. Quantity

Now, i want to give the Order table a PK, but i confused what should i use/add to the table so i can have PK. Or is there any other way?

Maybe this is a very basic question, but please any help would be nice.

Thank you,
Ippo

I don't know your data and your business but at a guess I'd expect (OrderNo, Item) to be unique - assuming Item means a SKU or product code of some kind.

Oh yea, sorry that i forgot to explain about the data.

The Sales Order consist of:
1. Order No. (PK) = the sales order number
2. Date = the date that the sales order issued
3. Client = the name of the client
4. etc

For example
Order No | Date | Client |
001 1-1-2009 A
002 2-4-2009 B
002 4-4-2009 C
004 6-9-2009 D


The Order table consist of:
1. Order No. (FK) = the sales order number from Sales Order (PK)
2. No = the order of the item on the list
3. Item = name of the item
4. Quantity = quantity of the item

For example:

Order No | No | Item | Qty
001 1 handphone 11
001 2 envelop 5
002 1 pen 12
002 2 post it 4
002 3 mouse 5
003 1 laptop 2

so, it's like that.......

thx

From your description, the Order No. and No columns of the Order table are unique so you can use the combination as the primary key.

If the No (item number) column changes when an item gets deleted or the items get renumbered for some reason this might cause a problem. In that case I would add an Identity column to the Order table and make that my primary key.

Hai,

I already resolve this one, i have 2 ways:
1. Using IDENTITY column (thx for the previous post)
2. Using Look-up table to set composite primary key.

Thank you,
Ippo

if you don't allow a customer order to have the same product appearing more than once on invoice then order no + product no is a natural key to choose
however some systems require products in a specific order (often the order the customer has specified them) so it may instead be order + row number. this helps the customer because the order is in the sort order they expect to see assuming they marry up your delivery to their internal order.

Look-up table to set composite primary key ??? Why use a look-up table?

It looks like you are trying to model a standard order-orderline relationship... the following has a great discussion on the topic:

Database Design - Many-to-many-Order System

I might also add that there doesn't seem to be any real requirement/benefit of using a natural composite key here, why not just use a surrogate orderline PK, and keep OrderID as your FK in the OrderLine table?