Dual Primary Key?
-
Hi Our ERP system has a stock master table. It consist of the fields 'warehouse', 'product' and a bunch of other fields relating to the stock item. A product can exist in various warehouses, so it is the warehouse +product combination that is unique. How would the primary key (if any) be set on this table?
-
Hi Our ERP system has a stock master table. It consist of the fields 'warehouse', 'product' and a bunch of other fields relating to the stock item. A product can exist in various warehouses, so it is the warehouse +product combination that is unique. How would the primary key (if any) be set on this table?
A primary key (often) consists out of multiple columns. I'd suggest putting the primary key on BOTH, and to add an autoincrement-field and make that unique. Use the autoincrement-column to make relations to other tables.
ALTER TABLE dbo.MasterTable ADD CONSTRAINT PK_MasterTable
PRIMARY KEY CLUSTERED (WareHouseId, ProductId);(And call the autoincrement "MasterTableId")
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
A primary key (often) consists out of multiple columns. I'd suggest putting the primary key on BOTH, and to add an autoincrement-field and make that unique. Use the autoincrement-column to make relations to other tables.
ALTER TABLE dbo.MasterTable ADD CONSTRAINT PK_MasterTable
PRIMARY KEY CLUSTERED (WareHouseId, ProductId);(And call the autoincrement "MasterTableId")
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Thanks Eddie, but you have totally lost me? If I lok at the two lines:
ADD CONSTRAINT PK_MasterTable
Prevents any duplicates in MasterTable column and
PRIMARY KEY CLUSTERED (WareHouseId, ProductId)
Stores the data physically sorted by WarehouseId then ProductID? Or are there three Primary keys now? If you look at the table in MSQuery, the Product is bold, so is this the primary key?
-
Thanks Eddie, but you have totally lost me? If I lok at the two lines:
ADD CONSTRAINT PK_MasterTable
Prevents any duplicates in MasterTable column and
PRIMARY KEY CLUSTERED (WareHouseId, ProductId)
Stores the data physically sorted by WarehouseId then ProductID? Or are there three Primary keys now? If you look at the table in MSQuery, the Product is bold, so is this the primary key?
Richard.Berry100 wrote:
Prevents any duplicates in MasterTable column
No, it doesn't; it's the part behind that which says "which type" of constraint. It's a single statement, not two separate statements.
Richard.Berry100 wrote:
PRIMARY KEY CLUSTERED (WareHouseId, ProductId)
Stores the data physically sorted by WarehouseId then ProductID?
Yup.
Richard.Berry100 wrote:
Or are there three Primary keys now?
One (compound) primary key, consisting of two columns, and the name of that constraint is "PK_MasterTable".
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
-
A primary key (often) consists out of multiple columns. I'd suggest putting the primary key on BOTH, and to add an autoincrement-field and make that unique. Use the autoincrement-column to make relations to other tables.
ALTER TABLE dbo.MasterTable ADD CONSTRAINT PK_MasterTable
PRIMARY KEY CLUSTERED (WareHouseId, ProductId);(And call the autoincrement "MasterTableId")
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]
Why would you not use WharehouseID-ProductID in a concatenated field as a primary key.
Never underestimate the power of human stupidity RAH
-
Why would you not use WharehouseID-ProductID in a concatenated field as a primary key.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
WharehouseID
I see what you did there. :laugh:
-
Why would you not use WharehouseID-ProductID in a concatenated field as a primary key.
Never underestimate the power of human stupidity RAH
Mycroft Holmes wrote:
Why would you not use WharehouseID-ProductID in a concatenated field as a primary key.
That's a possibility; then again, it introduces a concatenation-action, and we'd be storing redundant information. It'd also affect performance; having a large varchar-based key (as two bigints as Id's or Guids would be concatenated to a varchar) would be not-nice for your indexes. Or, in the words of my teacher; it would no longer be an atomic value.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^]