Production.ProductInventory Table

Description

Product inventory information.

Properties

Creation Date	27/10/2017 14:33
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	1069
Data Space Used	56.00 KB
Index Space Used	16.00 KB

Columns

Column Name	Description	Datatype	Length	Default
ProductID	Product identification number. Foreign key to Product.ProductID.	Integer	4
LocationID	Inventory location identification number. Foreign key to Location.LocationID.	SmallInt	2
Shelf	Storage compartment within an inventory location.	VarWChar	10
Bin	Storage container on a shelf in an inventory location.	UnsignedTinyInt	1
Quantity	Quantity of products in the inventory location.	SmallInt	2	((0))
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	GUID	16	(newid())
ModifiedDate	Date and time the record was last updated.	DBTimeStamp	4	(getdate())

Indexes

Index	Description	Primary	Unique
PK_ProductInventory_ProductID_LocationID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_ProductInventory_Bin	Check constraint [Bin] BETWEEN (0) AND (100)	([Bin]>=(0) AND [Bin]<=(100))
CK_ProductInventory_Shelf	Check constraint [Shelf] like '[A-Za-z]' OR [Shelf]='N/A'	([Shelf] like '[A-Za-z]' OR [Shelf]='N/A')

Relationships

Relationship	Description
FK_ProductInventory_Location_LocationID	Foreign key constraint referencing Location.LocationID.
FK_ProductInventory_Product_ProductID	Foreign key constraint referencing Product.ProductID.

Objects that depend on Production.ProductInventory

	Database Object	Object Type	Description	Dep Level
	dbo.ufnGetStock	User Defined Function	Scalar function returning the quantity of inventory in LocationID 6 (Miscellaneous Storage)for a specified ProductID.	1

Objects that Production.ProductInventory depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		2
Production.Location	Table	Product inventory and manufacturing locations.	1
dbo.Name	User Defined Data Type		2
Production.Product	Table	Products sold or used in the manfacturing of sold products.	1
Production.ProductCategory	Table	High-level product categorization.	3
Production.ProductModel	Table	Product model classification.	2
Production.ProductSubcategory	Table	Product subcategories. See ProductCategory table.	2
Production.UnitMeasure	Table	Unit of measure lookup table.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductInventory](
    [ProductID] [int] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [Shelf] [nvarchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
    [Bin] [tinyint] NOT NULL,
    [Quantity] [smallint] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ProductInventory_ProductID_LocationID] PRIMARY KEY CLUSTERED 
(
    [ProductID] ASC,
    [LocationID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

ALTER TABLE [Production].[ProductInventory] ADD  CONSTRAINT [DF_ProductInventory_Quantity]  DEFAULT ((0)) FOR [Quantity]
ALTER TABLE [Production].[ProductInventory] ADD  CONSTRAINT [DF_ProductInventory_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Production].[ProductInventory] ADD  CONSTRAINT [DF_ProductInventory_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Production].[ProductInventory]  WITH CHECK ADD  CONSTRAINT [FK_ProductInventory_Location_LocationID] FOREIGN KEY([LocationID])
REFERENCES [Production].[Location] ([LocationID])
ALTER TABLE [Production].[ProductInventory] CHECK CONSTRAINT [FK_ProductInventory_Location_LocationID]
ALTER TABLE [Production].[ProductInventory]  WITH CHECK ADD  CONSTRAINT [FK_ProductInventory_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Production].[ProductInventory] CHECK CONSTRAINT [FK_ProductInventory_Product_ProductID]
ALTER TABLE [Production].[ProductInventory]  WITH CHECK ADD  CONSTRAINT [CK_ProductInventory_Bin] CHECK  (([Bin]>=(0) AND [Bin]<=(100)))
ALTER TABLE [Production].[ProductInventory] CHECK CONSTRAINT [CK_ProductInventory_Bin]
ALTER TABLE [Production].[ProductInventory]  WITH CHECK ADD  CONSTRAINT [CK_ProductInventory_Shelf] CHECK  (([Shelf] like '[A-Za-z]' OR [Shelf]='N/A'))
ALTER TABLE [Production].[ProductInventory] CHECK CONSTRAINT [CK_ProductInventory_Shelf]

Related Objects