Production.ProductInventory

Object type: Table

Product inventory information.

Columns

Name Data type Nullable Description

ProductID int No Product identification number. Foreign key to Product.ProductID.

LocationID smallint No Inventory location identification number. Foreign key to Location.LocationID.

Shelf nvarchar(10) No Storage compartment within an inventory location.

Bin tinyint No Storage container on a shelf in an inventory location.

Quantity smallint No Default value: 0
Quantity of products in the inventory location.

rowguid uniqueidentifier No Default value: NEWID()
ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.

ModifiedDate datetime No Default value: GETDATE()
Date and time the record was last updated.

Constraints

Name Type Columns Description

DF_ProductInventory_Quantity DEFAULT Quantity Value: 0
Default constraint value of 0

DF_ProductInventory_rowguid DEFAULT rowguid Value: NEWID()
Default constraint value of NEWID()

DF_ProductInventory_ModifiedDate DEFAULT ModifiedDate Value: GETDATE()
Default constraint value of GETDATE()

CK_ProductInventory_Shelf CHECK N/A Value: (([Shelf] LIKE '[A-Za-z]') OR ([Shelf] = 'N/A') )
Check constraint [Shelf] like '[A-Za-z]' OR [Shelf]='N/A'

CK_ProductInventory_Bin CHECK N/A Value: ([Bin] BETWEEN 0 AND 100)
Check constraint [Bin] BETWEEN (0) AND (100)

PK_ProductInventory_ProductID_LocationID PRIMARY KEY ProductID
LocationID Clustered index created by a primary key constraint.

FK_ProductInventory_Location_LocationID FOREIGN KEY LocationID Reference table: Production.Location
Foreign key constraint referencing Location.LocationID.

FK_ProductInventory_Product_ProductID FOREIGN KEY ProductID Reference table: Production.Product
Foreign key constraint referencing Product.ProductID.

Indices

Name Description

AK_ProductInventory_rowguid Unique nonclustered index.Used to support replication samples.

IX_ProductInventory_ProductID Nonclustered index.

Triggers

Name Description

Production.uProductInventory AFTER UPDATE trigger setting the ModifiedDate column in the ProductInventory table to the current date.

Accessed by

Name Description

dbo.ufnGetStock Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.

Modified by

Name Description

Production.uProductInventory AFTER UPDATE trigger setting the ModifiedDate column in the ProductInventory table to the current date.

Applies to

Tables | User

Name	Data type	Nullable	Description
ProductID	int	No	Product identification number. Foreign key to Product.ProductID.
LocationID	smallint	No	Inventory location identification number. Foreign key to Location.LocationID.
Shelf	nvarchar(10)	No	Storage compartment within an inventory location.
Bin	tinyint	No	Storage container on a shelf in an inventory location.
Quantity	smallint	No	Default value: 0 Quantity of products in the inventory location.
rowguid	uniqueidentifier	No	Default value: NEWID() ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
ModifiedDate	datetime	No	Default value: GETDATE() Date and time the record was last updated.

Name	Type	Columns	Description
DF_ProductInventory_Quantity	DEFAULT	Quantity	Value: 0 Default constraint value of 0
DF_ProductInventory_rowguid	DEFAULT	rowguid	Value: NEWID() Default constraint value of NEWID()
DF_ProductInventory_ModifiedDate	DEFAULT	ModifiedDate	Value: GETDATE() Default constraint value of GETDATE()
CK_ProductInventory_Shelf	CHECK	N/A	Value: (([Shelf] LIKE '[A-Za-z]') OR ([Shelf] = 'N/A') ) Check constraint [Shelf] like '[A-Za-z]' OR [Shelf]='N/A'
CK_ProductInventory_Bin	CHECK	N/A	Value: ([Bin] BETWEEN 0 AND 100) Check constraint [Bin] BETWEEN (0) AND (100)
PK_ProductInventory_ProductID_LocationID	PRIMARY KEY	ProductID LocationID	Clustered index created by a primary key constraint.
FK_ProductInventory_Location_LocationID	FOREIGN KEY	LocationID	Reference table: Production.Location Foreign key constraint referencing Location.LocationID.
FK_ProductInventory_Product_ProductID	FOREIGN KEY	ProductID	Reference table: Production.Product Foreign key constraint referencing Product.ProductID.

Name	Description
AK_ProductInventory_rowguid	Unique nonclustered index.Used to support replication samples.
IX_ProductInventory_ProductID	Nonclustered index.

Name	Description
Production.uProductInventory	AFTER UPDATE trigger setting the ModifiedDate column in the ProductInventory table to the current date.

Name	Description
dbo.ufnGetStock	Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.