Dual Primary Key or Better Idea?

[Dante Shaheen]

Friends,

My database currently has two tables. One table logs in purchase orders and the primery key is the purchase order number.

The second table tracks the line items from the purchase orders and is tied back to the purchase order number.

I would like to prevent a line item number from a specific purchase order be accidentally repeated. However, if I make the line item number a primary key, I cannot enter the same line item for a different purchase order number.

Is there a simple way to be sure that the line item cannot be duplicated for a specific PO number.

Thanks for your help,

Dante

 

[gemma-the-husky]

you make the primary key of the orderlines tables consist of TWO fields - the order number AND the product line.

 

[Dante Shaheen]

Not sure exactly how to do this. Understand that several records may have the same order number but the line item number will be different. Won't the duplicate order number create a problem?

Thanks,

Dante

 

[RainLover]

My golden rule is that the Primary key is never available to the user.

The best Primary Key is Autonumber.

Do not use a Composite Primary Key. i.e. A Primary Key made up of more than one field.

You can create a Unique Index which allows you the set more than one Field (as a combination) as unique.

 

[RainLover]

I found this in my archives.

To set up a compound index in a table click on (little lightning bolt thingy at the top) of your table while in design view. Or View Indexes.
In the 1st empty row of the form that opens, name the index in the left hand column. I would use something like XXX, or whatever you like.
In the middle column of the same row, select the first field.
At the bottom left of the form, change 'Unique' to Yes.
In the row directly under XXX, leave the left hand column empty, and select the second field in the middle column.
You can add a third and fourth etc if you want
Close the Indexes form
You have created a composite index that will not allow duplication of the same fields.