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.
| Author |
Topic |
|
GaneshParam
Starting Member
1 Post |
 Posted - 2005-06-15 : 22:19:25
|
| My SupplierPrice table structure is as follows ( ItemCode varchar2(10), CompanyCode varchar2(5), SupplierCode varchar2(10), PurchaseUOM varchar2(5), Currency Number(3), PurchasePrice Number(10,2), EffectiveFromDate Date, EffectiveToDate Date ) Pur. Price is set for each ItemCode, CompanyCode, SupplierCode, PurchaseUOM, Currency, EffectiveFromDate, EffectiveToDate. Date range cannot be part of primary key as I can change the date range for an existing record. Also I cannot set my Primary Key on ItemCode, CompanyCode, SupplierCode, PurchaseUOM, Currency. So I came up with two alternatives for setting Primary Key. 1. Create an auto-generated column and have it as a primary key. 2. A SrNo field is added which is system generated. Then I set primary key as (ItemCode, CompanyCode, SupplierCode, PurchaseUOM, Currency, SrNo). Or you might suggest of a better solution My system should be faster to retrieve data based on 1. where clause on ItemCode 2. where clause on ItemCode, CompanyCode 3. where clause on ItemCode, CompanyCode, SupplierCode 4. where clause on ItemCode, CompanyCode, SupplierCode, PurchaseUOM, Currency Anyways I have created 3 non-unique indexes for the above three. There will be regular inserts to this table. Help me suggest the best alternative to create Primary Key. |
|
|
|
|
|
|
|
Setup Primary Key