AdventureWorks

AdventureWorks Sample OLTP Database30/03/2009
178.75 MB

Summary

• Tables (70)
  • dbo.AWBuildVersion
  • dbo.DatabaseLog
  • dbo.ErrorLog
  • HumanResources.Department
  • HumanResources.Employee
  • HumanResources.EmployeeAddress
  • HumanResources.EmployeeDepartmentHistory
  • HumanResources.EmployeePayHistory
  • HumanResources.JobCandidate
  • HumanResources.Shift
  • Person.Address
  • Person.AddressType
  • Person.Contact
  • Person.ContactType
  • Person.CountryRegion
  • Person.StateProvince
  • Production.BillOfMaterials
  • Production.Culture
  • Production.Document
  • Production.Illustration
  • Production.Location
  • Production.Product
  • Production.ProductCategory
  • Production.ProductCostHistory
  • Production.ProductDescription
  • Production.ProductDocument
  • Production.ProductInventory
  • Production.ProductListPriceHistory
  • Production.ProductModel
  • Production.ProductModelIllustration
  • Production.ProductModelProductDescriptionCulture
  • Production.ProductPhoto
  • Production.ProductProductPhoto
  • Production.ProductReview
  • Production.ProductSubcategory
  • Production.ScrapReason
  • Production.TransactionHistory
  • Production.TransactionHistoryArchive
  • Production.UnitMeasure
  • Production.WorkOrder
  • Production.WorkOrderRouting
  • Purchasing.ProductVendor
  • Purchasing.PurchaseOrderDetail
  • Purchasing.PurchaseOrderHeader
  • Purchasing.ShipMethod
  • Purchasing.Vendor
  • Purchasing.VendorAddress
  • Purchasing.VendorContact
  • Sales.ContactCreditCard
  • Sales.CountryRegionCurrency
  • Sales.CreditCard
  • Sales.Currency
  • Sales.CurrencyRate
  • Sales.Customer
  • Sales.CustomerAddress
  • Sales.Individual
  • Sales.SalesOrderDetail
  • Sales.SalesOrderHeader
  • Sales.SalesOrderHeaderSalesReason
  • Sales.SalesPerson
  • Sales.SalesPersonQuotaHistory
  • Sales.SalesReason
  • Sales.SalesTaxRate
  • Sales.SalesTerritory
  • Sales.SalesTerritoryHistory
  • Sales.ShoppingCartItem
  • Sales.SpecialOffer
  • Sales.SpecialOfferProduct
  • Sales.Store
  • Sales.StoreContact
• Views (17)
  • HumanResources.vEmployee
  • HumanResources.vEmployeeDepartment
  • HumanResources.vEmployeeDepartmentHistory
  • HumanResources.vJobCandidate
  • HumanResources.vJobCandidateEducation
  • HumanResources.vJobCandidateEmployment
  • Person.vAdditionalContactInfo
  • Person.vStateProvinceCountryRegion
  • Production.vProductAndDescription
  • Production.vProductModelCatalogDescription
  • Production.vProductModelInstructions
  • Purchasing.vVendor
  • Sales.vIndividualCustomer
  • Sales.vIndividualDemographics
  • Sales.vSalesPerson
  • Sales.vSalesPersonSalesByFiscalYears
  • Sales.vStoreWithDemographics
• Stored procedures (9)
  • dbo.uspGetBillOfMaterials
  • dbo.uspGetEmployeeManagers
  • dbo.uspGetManagerEmployees
  • dbo.uspGetWhereUsedProductID
  • dbo.uspLogError
  • dbo.uspPrintError
  • HumanResources.uspUpdateEmployeeHireInfo
  • HumanResources.uspUpdateEmployeeLogin
  • HumanResources.uspUpdateEmployeePersonalInfo
• User-defined functions (11)
  • dbo.ufnGetAccountingEndDate
  • dbo.ufnGetAccountingStartDate
  • dbo.ufnGetContactInformation
  • dbo.ufnGetDocumentStatusText
  • dbo.ufnGetProductDealerPrice
  • dbo.ufnGetProductListPrice
  • dbo.ufnGetProductStandardCost
  • dbo.ufnGetPurchaseOrderStatusText
  • dbo.ufnGetSalesOrderStatusText
  • dbo.ufnGetStock
  • dbo.ufnLeadingZeros
• User-defined types (6)
  • AccountNumber
  • Flag
  • Name
  • NameStyle
  • OrderNumber
  • Phone
• XML Schemas (7)
  • HumanResources.HRResumeSchemaCollection
  • Person.AdditionalContactInfoSchemaCollection
  • Production.ManuInstructionsSchemaCollection
  • Production.ProductDescriptionSchemaCollection
  • Sales.IndividualSurveySchemaCollection
  • Sales.StoreSurveySchemaCollection
  • sys.sys
• Users (2)
  • dbo
  • TestUser
• Roles (10)
  • db_accessadmin
  • db_backupoperator
  • db_datareader
  • db_datawriter
  • db_ddladmin
  • db_denydatareader
  • db_denydatawriter
  • db_owner
  • db_securityadmin
  • public

Tables

Table: dbo.AWBuildVersion

Fields

	Field name	Data type	Nullable	Default value	Field description
	SystemInformationID	tinyint			Primary key for AWBuildVersion records.
	Database Version	nvarchar (25)			Version number of the database in 9.yy.mm.dd.00 format.
	VersionDate	datetime			Date and time the record was last updated.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_AWBuildVersion_SystemInformationID	SystemInformationID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[AWBuildVersion](
    [SystemInformationID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Database Version] [nvarchar](25) COLLATE Latin1_General_CS_AS NOT NULL,
    [VersionDate] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: dbo.DatabaseLog

Fields

	Field name	Data type	Nullable	Default value	Field description
	DatabaseLogID	int			Primary key for DatabaseLog records.
	PostTime	datetime			The date and time the DDL change occurred.
	DatabaseUser	nvarchar (128)			The user who implemented the DDL change.
	Event	nvarchar (128)			The type of DDL statement that was executed.
	Schema	nvarchar (128)	Yes		The schema to which the changed object belongs.
	Object	nvarchar (128)	Yes		The object that was changed by the DDL statment.
	TSQL	nvarchar			The exact Transact-SQL statement that was executed.
	XmlEvent	xml			The raw XML data generated by database trigger.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_DatabaseLog_DatabaseLogID	DatabaseLogID	ASC	Yes	NONCLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[DatabaseLog](
    [DatabaseLogID] [int] IDENTITY(1,1) NOT NULL,
    [PostTime] [datetime] NOT NULL,
    [DatabaseUser] [sysname] COLLATE Latin1_General_CS_AS NOT NULL,
    [Event] [sysname] COLLATE Latin1_General_CS_AS NOT NULL,
    [Schema] [sysname] COLLATE Latin1_General_CS_AS NULL,
    [Object] [sysname] COLLATE Latin1_General_CS_AS NULL,
    [TSQL] [nvarchar](max) COLLATE Latin1_General_CS_AS NOT NULL,
    [XmlEvent] [xml] NOT NULL
) ON [PRIMARY]

Table: dbo.ErrorLog

Fields

	Field name	Data type	Nullable	Default value	Field description
	ErrorLogID	int			Primary key for ErrorLog records.
	ErrorTime	datetime		(getdate())	The date and time at which the error occurred.
	UserName	nvarchar (128)			The user who executed the batch in which the error occurred.
	ErrorNumber	int			The error number of the error that occurred.
	ErrorSeverity	int	Yes		The severity of the error that occurred.
	ErrorState	int	Yes		The state number of the error that occurred.
	ErrorProcedure	nvarchar (126)	Yes		The name of the stored procedure or trigger where the error occurred.
	ErrorLine	int	Yes		The line number at which the error occurred.
	ErrorMessage	nvarchar (4000)			The message text of the error that occurred.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ErrorLog_ErrorLogID	ErrorLogID	ASC	Yes	CLUSTERED

Objects that depend on dbo.ErrorLog

dbo.uspLogError

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [dbo].[ErrorLog](
    [ErrorLogID] [int] IDENTITY(1,1) NOT NULL,
    [ErrorTime] [datetime] NOT NULL,
    [UserName] [sysname] COLLATE Latin1_General_CS_AS NOT NULL,
    [ErrorNumber] [int] NOT NULL,
    [ErrorSeverity] [int] NULL,
    [ErrorState] [int] NULL,
    [ErrorProcedure] [nvarchar](126) COLLATE Latin1_General_CS_AS NULL,
    [ErrorLine] [int] NULL,
    [ErrorMessage] [nvarchar](4000) COLLATE Latin1_General_CS_AS NOT NULL
) ON [PRIMARY]

Table: HumanResources.Department

Fields

	Field name	Data type	Nullable	Default value	Field description
	DepartmentID	smallint			Primary key for Department records.
	Name	Name			Name of the department.
	GroupName	Name			Name of the group to which the department belongs.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
HumanResources.EmployeeDepartmentHistory	FK_EmployeeDepartmentHistory_Department_DepartmentID	PK_Department_DepartmentID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Department_Name	Name	ASC	Yes	NONCLUSTERED
PK_Department_DepartmentID	DepartmentID	ASC	Yes	CLUSTERED

Objects that depend on HumanResources.Department

HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[Department](
    [DepartmentID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [GroupName] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.Employee

Fields

	Field name	Data type	Nullable	Default value	Field description
	EmployeeID	int			Primary key for Employee records.
	NationalIDNumber	nvarchar (15)			Unique national identification number such as a social security number.
	ContactID	int			Identifies the employee in the Contact table. Foreign key to Contact.ContactID.
	LoginID	nvarchar (256)			Network login.
	ManagerID	int	Yes		Manager to whom the employee is assigned. Foreign Key to Employee.M
	Title	nvarchar (50)			Work title such as Buyer or Sales Representative.
	BirthDate	datetime			Date of birth.
	MaritalStatus	nchar (1)			M = Married, S = Single
	Gender	nchar (1)			M = Male, F = Female
	HireDate	datetime			Employee hired on this date.
	SalariedFlag	Flag		((1))	Job classification. 0 = Hourly, not exempt from collective bargaining. 1 = Salaried, exempt from collective bargaining.
	VacationHours	smallint		((0))	Number of available vacation hours.
	SickLeaveHours	smallint		((0))	Number of available sick leave hours.
	CurrentFlag	Flag		((1))	0 = Inactive, 1 = Active
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_Employee_Contact_ContactID	ContactID	PK_Contact_ContactID (Person.Contact)	Foreign key constraint referencing Contact.ContactID.
FK_Employee_Employee_ManagerID	ManagerID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.ManagerID.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
HumanResources.JobCandidate	FK_JobCandidate_Employee_EmployeeID	PK_Employee_EmployeeID
Purchasing.PurchaseOrderHeader	FK_PurchaseOrderHeader_Employee_EmployeeID	PK_Employee_EmployeeID
Sales.SalesPerson	FK_SalesPerson_Employee_SalesPersonID	PK_Employee_EmployeeID
HumanResources.Employee	FK_Employee_Employee_ManagerID	PK_Employee_EmployeeID
HumanResources.EmployeeDepartmentHistory	FK_EmployeeDepartmentHistory_Employee_EmployeeID	PK_Employee_EmployeeID
HumanResources.EmployeeAddress	FK_EmployeeAddress_Employee_EmployeeID	PK_Employee_EmployeeID
HumanResources.EmployeePayHistory	FK_EmployeePayHistory_Employee_EmployeeID	PK_Employee_EmployeeID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Employee_LoginID	LoginID	ASC	Yes	NONCLUSTERED
AK_Employee_NationalIDNumber	NationalIDNumber	ASC	Yes	NONCLUSTERED
AK_Employee_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_Employee_ManagerID	ManagerID	ASC		NONCLUSTERED
PK_Employee_EmployeeID	EmployeeID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Employee_BirthDate	BirthDate	([BirthDate]>='1930-01-01' AND [BirthDate]<=dateadd(year,(-18),getdate()))
CK_Employee_MaritalStatus	MaritalStatus	(upper([MaritalStatus])='S' OR upper([MaritalStatus])='M')
CK_Employee_HireDate	HireDate	([HireDate]>='1996-07-01' AND [HireDate]<=dateadd(day,(1),getdate()))
CK_Employee_Gender	Gender	(upper([Gender])='F' OR upper([Gender])='M')
CK_Employee_VacationHours	VacationHours	([VacationHours]>=(-40) AND [VacationHours]<=(240))
CK_Employee_SickLeaveHours	SickLeaveHours	([SickLeaveHours]>=(0) AND [SickLeaveHours]<=(120))

Triggers

Trigger name:	HumanResources.dEmployee
Description:	INSTEAD OF DELETE trigger which keeps Employees from being deleted.
Creation date:	26 Apr 2006
Trigger type:	INSTEAD OF DELETE
Trigger active:	Yes

CREATE TRIGGER [HumanResources].[dEmployee] ON [HumanResources].[Employee] 
INSTEAD OF DELETE NOT FOR REPLICATION AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN
        RAISERROR
            (N'Employees cannot be deleted. They can only be marked as not current.', -- Message
            10, -- Severity.
            1); -- State.

        -- Rollback any active or uncommittable transactions
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END
    END;
END;

Objects that depend on HumanResources.Employee

dbo.uspGetEmployeeManagers
dbo.uspGetManagerEmployees
HumanResources.uspUpdateEmployeeHireInfo
HumanResources.uspUpdateEmployeeLogin
HumanResources.uspUpdateEmployeePersonalInfo
HumanResources.vEmployee
HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory
Sales.vSalesPerson
Sales.vSalesPersonSalesByFiscalYears

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[Employee](
    [EmployeeID] [int] IDENTITY(1,1) NOT NULL,
    [NationalIDNumber] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL,
    [ContactID] [int] NOT NULL,
    [LoginID] [nvarchar](256) COLLATE Latin1_General_CS_AS NOT NULL,
    [ManagerID] [int] NULL,
    [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [BirthDate] [datetime] NOT NULL,
    [MaritalStatus] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [Gender] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [HireDate] [datetime] NOT NULL,
    [SalariedFlag] [dbo].[Flag] NOT NULL,
    [VacationHours] [smallint] NOT NULL,
    [SickLeaveHours] [smallint] NOT NULL,
    [CurrentFlag] [dbo].[Flag] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.EmployeeAddress

Fields

	Field name	Data type	Nullable	Default value	Field description
	EmployeeID	int			Primary key. Foreign key to Employee.EmployeeID.
	AddressID	int			Primary key. Foreign key to Address.AddressID.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_EmployeeAddress_Employee_EmployeeID	EmployeeID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeAddress_Address_AddressID	AddressID	PK_Address_AddressID (Person.Address)	Foreign key constraint referencing Address.AddressID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_EmployeeAddress_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_EmployeeAddress_EmployeeID_AddressID	EmployeeID	ASC	Yes	CLUSTERED
PK_EmployeeAddress_EmployeeID_AddressID	AddressID	ASC	Yes	CLUSTERED

Objects that depend on HumanResources.EmployeeAddress

HumanResources.vEmployee
Sales.vSalesPerson

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[EmployeeAddress](
    [EmployeeID] [int] NOT NULL,
    [AddressID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.EmployeeDepartmentHistory

Fields

	Field name	Data type	Nullable	Default value	Field description
	EmployeeID	int			Employee identification number. Foreign key to Employee.EmployeeID.
	DepartmentID	smallint			Department in which the employee worked including currently. Foreign key to Department.DepartmentID.
	ShiftID	tinyint			Identifies which 8-hour shift the employee works. Foreign key to Shift.Shift.ID.
	StartDate	datetime			Date the employee started work in the department.
	EndDate	datetime	Yes		Date the employee left the department. NULL = Current department.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_EmployeeDepartmentHistory_Employee_EmployeeID	EmployeeID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.EmployeeID.
FK_EmployeeDepartmentHistory_Department_DepartmentID	DepartmentID	PK_Department_DepartmentID (HumanResources.Department)	Foreign key constraint referencing Department.DepartmentID.
FK_EmployeeDepartmentHistory_Shift_ShiftID	ShiftID	PK_Shift_ShiftID (HumanResources.Shift)	Foreign key constraint referencing Shift.ShiftID

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_EmployeeDepartmentHistory_DepartmentID	DepartmentID	ASC		NONCLUSTERED
IX_EmployeeDepartmentHistory_ShiftID	ShiftID	ASC		NONCLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	EmployeeID	ASC	Yes	CLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	DepartmentID	ASC	Yes	CLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	ShiftID	ASC	Yes	CLUSTERED
PK_EmployeeDepartmentHistory_EmployeeID_StartDate_DepartmentID	StartDate	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_EmployeeDepartmentHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_EmployeeDepartmentHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Objects that depend on HumanResources.EmployeeDepartmentHistory

HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[EmployeeDepartmentHistory](
    [EmployeeID] [int] NOT NULL,
    [DepartmentID] [smallint] NOT NULL,
    [ShiftID] [tinyint] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.EmployeePayHistory

Fields

	Field name	Data type	Nullable	Default value	Field description
	EmployeeID	int			Employee identification number. Foreign key to Employee.EmployeeID.
	RateChangeDate	datetime			Date the change in pay is effective
	Rate	money			Salary hourly rate.
	PayFrequency	tinyint			1 = Salary received monthly, 2 = Salary received biweekly
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_EmployeePayHistory_Employee_EmployeeID	EmployeeID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.EmployeeID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_EmployeePayHistory_EmployeeID_RateChangeDate	EmployeeID	ASC	Yes	CLUSTERED
PK_EmployeePayHistory_EmployeeID_RateChangeDate	RateChangeDate	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_EmployeePayHistory_PayFrequency	PayFrequency	([PayFrequency]=(2) OR [PayFrequency]=(1))
CK_EmployeePayHistory_Rate	Rate	([Rate]>=(6.50) AND [Rate]<=(200.00))

Objects that depend on HumanResources.EmployeePayHistory

HumanResources.uspUpdateEmployeeHireInfo

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[EmployeePayHistory](
    [EmployeeID] [int] NOT NULL,
    [RateChangeDate] [datetime] NOT NULL,
    [Rate] [money] NOT NULL,
    [PayFrequency] [tinyint] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.JobCandidate

Fields

	Field name	Data type	Nullable	Default value	Field description
	JobCandidateID	int			Primary key for JobCandidate records.
	EmployeeID	int	Yes		Employee identification number if applicant was hired. Foreign key to Employee.EmployeeID.
	Resume	xml	Yes		Résumé in XML format.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_JobCandidate_Employee_EmployeeID	EmployeeID	PK_Employee_EmployeeID (HumanResources.Employee)	Foreign key constraint referencing Employee.EmployeeID.

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_JobCandidate_EmployeeID	EmployeeID	ASC		NONCLUSTERED
PK_JobCandidate_JobCandidateID	JobCandidateID	ASC	Yes	CLUSTERED

Objects that depend on HumanResources.JobCandidate

HumanResources.vJobCandidate
HumanResources.vJobCandidateEducation
HumanResources.vJobCandidateEmployment

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[JobCandidate](
    [JobCandidateID] [int] IDENTITY(1,1) NOT NULL,
    [EmployeeID] [int] NULL,
    [Resume] [xml](CONTENT [HumanResources].[HRResumeSchemaCollection]) NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: HumanResources.Shift

Fields

	Field name	Data type	Nullable	Default value	Field description
	ShiftID	tinyint			Primary key for Shift records.
	Name	Name			Shift description.
	StartTime	datetime			Shift start time.
	EndTime	datetime			Shift end time.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
HumanResources.EmployeeDepartmentHistory	FK_EmployeeDepartmentHistory_Shift_ShiftID	PK_Shift_ShiftID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Shift_Name	Name	ASC	Yes	NONCLUSTERED
AK_Shift_StartTime_EndTime	StartTime	ASC	Yes	NONCLUSTERED
AK_Shift_StartTime_EndTime	EndTime	ASC	Yes	NONCLUSTERED
PK_Shift_ShiftID	ShiftID	ASC	Yes	CLUSTERED

Objects that depend on HumanResources.Shift

HumanResources.vEmployeeDepartmentHistory

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [HumanResources].[Shift](
    [ShiftID] [tinyint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [StartTime] [datetime] NOT NULL,
    [EndTime] [datetime] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.Address

Fields

	Field name	Data type	Nullable	Default value	Field description
	AddressID	int			Primary key for Address records.
	AddressLine1	nvarchar (60)			First street address line.
	AddressLine2	nvarchar (60)	Yes		Second street address line.
	City	nvarchar (30)			Name of the city.
	StateProvinceID	int			Unique identification number for the state or province. Foreign key to StateProvince table.
	PostalCode	nvarchar (15)			Postal code for the street address.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_Address_StateProvince_StateProvinceID	StateProvinceID	PK_StateProvince_StateProvinceID (Person.StateProvince)	Foreign key constraint referencing StateProvince.StateProvinceID.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Sales.SalesOrderHeader	FK_SalesOrderHeader_Address_BillToAddressID	PK_Address_AddressID
Purchasing.VendorAddress	FK_VendorAddress_Address_AddressID	PK_Address_AddressID
Sales.CustomerAddress	FK_CustomerAddress_Address_AddressID	PK_Address_AddressID
HumanResources.EmployeeAddress	FK_EmployeeAddress_Address_AddressID	PK_Address_AddressID
Sales.SalesOrderHeader	FK_SalesOrderHeader_Address_ShipToAddressID	PK_Address_AddressID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Address_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	AddressLine1	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	AddressLine2	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	City	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	StateProvinceID	ASC	Yes	NONCLUSTERED
IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode	PostalCode	ASC	Yes	NONCLUSTERED
IX_Address_StateProvinceID	StateProvinceID	ASC		NONCLUSTERED
PK_Address_AddressID	AddressID	ASC	Yes	CLUSTERED

Objects that depend on Person.Address

HumanResources.vEmployee
Purchasing.vVendor
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Address](
    [AddressID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [AddressLine1] [nvarchar](60) COLLATE Latin1_General_CS_AS NOT NULL,
    [AddressLine2] [nvarchar](60) COLLATE Latin1_General_CS_AS NULL,
    [City] [nvarchar](30) COLLATE Latin1_General_CS_AS NOT NULL,
    [StateProvinceID] [int] NOT NULL,
    [PostalCode] [nvarchar](15) COLLATE Latin1_General_CS_AS NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.AddressType

Fields

	Field name	Data type	Nullable	Default value	Field description
	AddressTypeID	int			Primary key for AddressType records.
	Name	Name			Address type description. For example, Billing, Home, or Shipping.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Sales.CustomerAddress	FK_CustomerAddress_AddressType_AddressTypeID	PK_AddressType_AddressTypeID
Purchasing.VendorAddress	FK_VendorAddress_AddressType_AddressTypeID	PK_AddressType_AddressTypeID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_AddressType_Name	Name	ASC	Yes	NONCLUSTERED
AK_AddressType_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_AddressType_AddressTypeID	AddressTypeID	ASC	Yes	CLUSTERED

Objects that depend on Person.AddressType

Sales.vIndividualCustomer
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[AddressType](
    [AddressTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.Contact

Fields

	Field name	Data type	Nullable	Default value	Field description
	ContactID	int			Primary key for Contact records.
	NameStyle	NameStyle		((0))	0 = The data in FirstName and LastName are stored in western style (first name, last name) order. 1 = Eastern style (last name, first name) order.
	Title	nvarchar (8)	Yes		A courtesy title. For example, Mr. or Ms.
	FirstName	Name			First name of the person.
	MiddleName	Name	Yes		Middle name or middle initial of the person.
	LastName	Name			Last name of the person.
	Suffix	nvarchar (10)	Yes		Surname suffix. For example, Sr. or Jr.
	EmailAddress	nvarchar (50)	Yes		E-mail address for the person.
	EmailPromotion	int		((0))	0 = Contact does not wish to receive e-mail promotions, 1 = Contact does wish to receive e-mail promotions from AdventureWorks, 2 = Contact does wish to receive e-mail promotions from AdventureWorks and selected partners.
	Phone	Phone	Yes		Phone number associated with the person.
	PasswordHash	varchar (128)			Password for the e-mail account.
	PasswordSalt	varchar (10)			Random value concatenated with the password string before the password is hashed.
	AdditionalContactInfo	xml	Yes		Additional contact information about the person stored in xml format.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Sales.SalesOrderHeader	FK_SalesOrderHeader_Contact_ContactID	PK_Contact_ContactID
Sales.StoreContact	FK_StoreContact_Contact_ContactID	PK_Contact_ContactID
Purchasing.VendorContact	FK_VendorContact_Contact_ContactID	PK_Contact_ContactID
Sales.ContactCreditCard	FK_ContactCreditCard_Contact_ContactID	PK_Contact_ContactID
HumanResources.Employee	FK_Employee_Contact_ContactID	PK_Contact_ContactID
Sales.Individual	FK_Individual_Contact_ContactID	PK_Contact_ContactID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Contact_rowguid	rowguid	ASC	Yes	NONCLUSTERED
IX_Contact_EmailAddress	EmailAddress	ASC		NONCLUSTERED
PK_Contact_ContactID	ContactID	ASC	Yes	CLUSTERED
PXML_Contact_AddContact	AdditionalContactInfo	ASC		XML

Check constraints

Check name	Column name	Check expresion
CK_Contact_EmailPromotion	EmailPromotion	([EmailPromotion]>=(0) AND [EmailPromotion]<=(2))

Objects that depend on Person.Contact

dbo.uspGetEmployeeManagers
dbo.uspGetManagerEmployees
HumanResources.vEmployee
HumanResources.vEmployeeDepartment
HumanResources.vEmployeeDepartmentHistory
Person.vAdditionalContactInfo
Purchasing.vVendor
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vSalesPersonSalesByFiscalYears
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[Contact](
    [ContactID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
    [NameStyle] [dbo].[NameStyle] NOT NULL,
    [Title] [nvarchar](8) COLLATE Latin1_General_CS_AS NULL,
    [FirstName] [dbo].[Name] NOT NULL,
    [MiddleName] [dbo].[Name] NULL,
    [LastName] [dbo].[Name] NOT NULL,
    [Suffix] [nvarchar](10) COLLATE Latin1_General_CS_AS NULL,
    [EmailAddress] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL,
    [EmailPromotion] [int] NOT NULL,
    [Phone] [dbo].[Phone] NULL,
    [PasswordHash] [varchar](128) COLLATE Latin1_General_CS_AS NOT NULL,
    [PasswordSalt] [varchar](10) COLLATE Latin1_General_CS_AS NOT NULL,
    [AdditionalContactInfo] [xml](CONTENT [Person].[AdditionalContactInfoSchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.ContactType

Fields

	Field name	Data type	Nullable	Default value	Field description
	ContactTypeID	int			Primary key for ContactType records.
	Name	Name			Contact type description.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Purchasing.VendorContact	FK_VendorContact_ContactType_ContactTypeID	PK_ContactType_ContactTypeID
Sales.StoreContact	FK_StoreContact_ContactType_ContactTypeID	PK_ContactType_ContactTypeID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_ContactType_Name	Name	ASC	Yes	NONCLUSTERED
PK_ContactType_ContactTypeID	ContactTypeID	ASC	Yes	CLUSTERED

Objects that depend on Person.ContactType

Purchasing.vVendor
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[ContactType](
    [ContactTypeID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.CountryRegion

Fields

	Field name	Data type	Nullable	Default value	Field description
	CountryRegionCode	nvarchar (3)			ISO standard code for countries and regions.
	Name	Name			Country or region name.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Person.StateProvince	FK_StateProvince_CountryRegion_CountryRegionCode	PK_CountryRegion_CountryRegionCode
Sales.CountryRegionCurrency	FK_CountryRegionCurrency_CountryRegion_CountryRegionCode	PK_CountryRegion_CountryRegionCode

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_CountryRegion_Name	Name	ASC	Yes	NONCLUSTERED
PK_CountryRegion_CountryRegionCode	CountryRegionCode	ASC	Yes	CLUSTERED

Objects that depend on Person.CountryRegion

HumanResources.vEmployee
Person.vStateProvinceCountryRegion
Purchasing.vVendor
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[CountryRegion](
    [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Person.StateProvince

Fields

	Field name	Data type	Nullable	Default value	Field description
	StateProvinceID	int			Primary key for StateProvince records.
	StateProvinceCode	nchar (3)			ISO standard state or province code.
	CountryRegionCode	nvarchar (3)			ISO standard country or region code. Foreign key to CountryRegion.CountryRegionCode.
	IsOnlyStateProvinceFlag	Flag		((1))	0 = StateProvinceCode exists. 1 = StateProvinceCode unavailable, using CountryRegionCode.
	Name	Name			State or province description.
	TerritoryID	int			ID of the territory in which the state or province is located. Foreign key to SalesTerritory.SalesTerritoryID.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_StateProvince_CountryRegion_CountryRegionCode	CountryRegionCode	PK_CountryRegion_CountryRegionCode (Person.CountryRegion)	Foreign key constraint referencing CountryRegion.CountryRegionCode.
FK_StateProvince_SalesTerritory_TerritoryID	TerritoryID	PK_SalesTerritory_TerritoryID (Sales.SalesTerritory)	Foreign key constraint referencing SalesTerritory.TerritoryID.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Sales.SalesTaxRate	FK_SalesTaxRate_StateProvince_StateProvinceID	PK_StateProvince_StateProvinceID
Person.Address	FK_Address_StateProvince_StateProvinceID	PK_StateProvince_StateProvinceID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_StateProvince_Name	Name	ASC	Yes	NONCLUSTERED
AK_StateProvince_rowguid	rowguid	ASC	Yes	NONCLUSTERED
AK_StateProvince_StateProvinceCode_CountryRegionCode	StateProvinceCode	ASC	Yes	NONCLUSTERED
AK_StateProvince_StateProvinceCode_CountryRegionCode	CountryRegionCode	ASC	Yes	NONCLUSTERED
PK_StateProvince_StateProvinceID	StateProvinceID	ASC	Yes	CLUSTERED

Objects that depend on Person.StateProvince

HumanResources.vEmployee
Person.vStateProvinceCountryRegion
Purchasing.vVendor
Sales.vIndividualCustomer
Sales.vSalesPerson
Sales.vStoreWithDemographics

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Person].[StateProvince](
    [StateProvinceID] [int] IDENTITY(1,1) NOT NULL,
    [StateProvinceCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [CountryRegionCode] [nvarchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [IsOnlyStateProvinceFlag] [dbo].[Flag] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [TerritoryID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.BillOfMaterials

Fields

	Field name	Data type	Nullable	Default value	Field description
	BillOfMaterialsID	int			Primary key for BillOfMaterials records.
	ProductAssemblyID	int	Yes		Parent product identification number. Foreign key to Product.ProductID.
	ComponentID	int			Component identification number. Foreign key to Product.ProductID.
	StartDate	datetime		(getdate())	Date the component started being used in the assembly item.
	EndDate	datetime	Yes		Date the component stopped being used in the assembly item.
	UnitMeasureCode	nchar (3)			Standard code identifying the unit of measure for the quantity.
	BOMLevel	smallint			Indicates the depth the component is from its parent (AssemblyID).
	PerAssemblyQty	decimal (8.2)		((1.00))	Quantity of the component needed to create the assembly.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_BillOfMaterials_Product_ProductAssemblyID	ProductAssemblyID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductAssemblyID.
FK_BillOfMaterials_Product_ComponentID	ComponentID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ComponentID.
FK_BillOfMaterials_UnitMeasure_UnitMeasureCode	UnitMeasureCode	PK_UnitMeasure_UnitMeasureCode (Production.UnitMeasure)	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	ProductAssemblyID	ASC	Yes	CLUSTERED
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	ComponentID	ASC	Yes	CLUSTERED
AK_BillOfMaterials_ProductAssemblyID_ComponentID_StartDate	StartDate	ASC	Yes	CLUSTERED
IX_BillOfMaterials_UnitMeasureCode	UnitMeasureCode	ASC		NONCLUSTERED
PK_BillOfMaterials_BillOfMaterialsID	BillOfMaterialsID	ASC	Yes	NONCLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_BillOfMaterials_EndDate	StartDate	([EndDate]>[StartDate] OR [EndDate] IS NULL)
CK_BillOfMaterials_EndDate	EndDate	([EndDate]>[StartDate] OR [EndDate] IS NULL)
CK_BillOfMaterials_ProductAssemblyID	ProductAssemblyID	([ProductAssemblyID]<>[ComponentID])
CK_BillOfMaterials_ProductAssemblyID	ComponentID	([ProductAssemblyID]<>[ComponentID])
CK_BillOfMaterials_BOMLevel	ProductAssemblyID	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
CK_BillOfMaterials_BOMLevel	BOMLevel	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
CK_BillOfMaterials_BOMLevel	PerAssemblyQty	([ProductAssemblyID] IS NULL AND [BOMLevel]=(0) AND [PerAssemblyQty]=(1.00) OR [ProductAssemblyID] IS NOT NULL AND [BOMLevel]>=(1))
CK_BillOfMaterials_PerAssemblyQty	PerAssemblyQty	([PerAssemblyQty]>=(1.00))

Objects that depend on Production.BillOfMaterials

dbo.uspGetBillOfMaterials
dbo.uspGetWhereUsedProductID

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[BillOfMaterials](
    [BillOfMaterialsID] [int] IDENTITY(1,1) NOT NULL,
    [ProductAssemblyID] [int] NULL,
    [ComponentID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [BOMLevel] [smallint] NOT NULL,
    [PerAssemblyQty] [decimal](8, 2) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Culture

Fields

	Field name	Data type	Nullable	Default value	Field description
	CultureID	nchar (6)			Primary key for Culture records.
	Name	Name			Culture description.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.ProductModelProductDescriptionCulture	FK_ProductModelProductDescriptionCulture_Culture_CultureID	PK_Culture_CultureID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Culture_Name	Name	ASC	Yes	NONCLUSTERED
PK_Culture_CultureID	CultureID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Culture](
    [CultureID] [nchar](6) COLLATE Latin1_General_CS_AS NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Document

Fields

	Field name	Data type	Nullable	Default value	Field description
	DocumentID	int			Primary key for Document records.
	Title	nvarchar (50)			Title of the document.
	FileName	nvarchar (400)			Directory path and file name of the document
	FileExtension	nvarchar (8)			File extension indicating the document type. For example, .doc or .txt.
	Revision	nchar (5)			Revision number of the document.
	ChangeNumber	int		((0))	Engineering change approval number.
	Status	tinyint			1 = Pending approval, 2 = Approved, 3 = Obsolete
	DocumentSummary	nvarchar	Yes		Document abstract.
	Document	varbinary	Yes		Complete document.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.ProductDocument	FK_ProductDocument_Document_DocumentID	PK_Document_DocumentID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Document_FileName_Revision	FileName	ASC	Yes	NONCLUSTERED
AK_Document_FileName_Revision	Revision	ASC	Yes	NONCLUSTERED
PK_Document_DocumentID	DocumentID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Document_Status	Status	([Status]>=(1) AND [Status]<=(3))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Document](
    [DocumentID] [int] IDENTITY(1,1) NOT NULL,
    [Title] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileName] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL,
    [FileExtension] [nvarchar](8) COLLATE Latin1_General_CS_AS NOT NULL,
    [Revision] [nchar](5) COLLATE Latin1_General_CS_AS NOT NULL,
    [ChangeNumber] [int] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [DocumentSummary] [nvarchar](max) COLLATE Latin1_General_CS_AS NULL,
    [Document] [varbinary](max) NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Illustration

Fields

	Field name	Data type	Nullable	Default value	Field description
	IllustrationID	int			Primary key for Illustration records.
	Diagram	xml	Yes		Illustrations used in manufacturing instructions. Stored as XML.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.ProductModelIllustration	FK_ProductModelIllustration_Illustration_IllustrationID	PK_Illustration_IllustrationID

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_Illustration_IllustrationID	IllustrationID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Illustration](
    [IllustrationID] [int] IDENTITY(1,1) NOT NULL,
    [Diagram] [xml] NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Location

Fields

	Field name	Data type	Nullable	Default value	Field description
	LocationID	smallint			Primary key for Location records.
	Name	Name			Location description.
	CostRate	smallmoney		((0.00))	Standard hourly cost of the manufacturing location.
	Availability	decimal (8.2)		((0.00))	Work capacity (in hours) of the manufacturing location.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.ProductInventory	FK_ProductInventory_Location_LocationID	PK_Location_LocationID
Production.WorkOrderRouting	FK_WorkOrderRouting_Location_LocationID	PK_Location_LocationID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Location_Name	Name	ASC	Yes	NONCLUSTERED
PK_Location_LocationID	LocationID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Location_CostRate	CostRate	([CostRate]>=(0.00))
CK_Location_Availability	Availability	([Availability]>=(0.00))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Location](
    [LocationID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CostRate] [smallmoney] NOT NULL,
    [Availability] [decimal](8, 2) NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.Product

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductID	int			Primary key for Product records.
	Name	Name			Name of the product.
	ProductNumber	nvarchar (25)			Unique product identification number.
	MakeFlag	Flag		((1))	0 = Product is purchased, 1 = Product is manufactured in-house.
	FinishedGoodsFlag	Flag		((1))	0 = Product is not a salable item. 1 = Product is salable.
	Color	nvarchar (15)	Yes		Product color.
	SafetyStockLevel	smallint			Minimum inventory quantity.
	ReorderPoint	smallint			Inventory level that triggers a purchase order or work order.
	StandardCost	money			Standard cost of the product.
	ListPrice	money			Selling price.
	Size	nvarchar (5)	Yes		Product size.
	SizeUnitMeasureCode	nchar (3)	Yes		Unit of measure for Size column.
	WeightUnitMeasureCode	nchar (3)	Yes		Unit of measure for Weight column.
	Weight	decimal (8.2)	Yes		Product weight.
	DaysToManufacture	int			Number of days required to manufacture the product.
	ProductLine	nchar (2)	Yes		R = Road, M = Mountain, T = Touring, S = Standard
	Class	nchar (2)	Yes		H = High, M = Medium, L = Low
	Style	nchar (2)	Yes		W = Womens, M = Mens, U = Universal
	ProductSubcategoryID	int	Yes		Product is a member of this product subcategory. Foreign key to ProductSubCategory.ProductSubCategoryID.
	ProductModelID	int	Yes		Product is a member of this product model. Foreign key to ProductModel.ProductModelID.
	SellStartDate	datetime			Date the product was available for sale.
	SellEndDate	datetime	Yes		Date the product was no longer available for sale.
	DiscontinuedDate	datetime	Yes		Date the product was discontinued.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_Product_UnitMeasure_SizeUnitMeasureCode	SizeUnitMeasureCode	PK_UnitMeasure_UnitMeasureCode (Production.UnitMeasure)	Foreign key constraint referencing UnitMeasure.UnitMeasureCode
FK_Product_UnitMeasure_WeightUnitMeasureCode	WeightUnitMeasureCode	PK_UnitMeasure_UnitMeasureCode (Production.UnitMeasure)	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.
FK_Product_ProductSubcategory_ProductSubcategoryID	ProductSubcategoryID	PK_ProductSubcategory_ProductSubcategoryID (Production.ProductSubcategory)	Foreign key constraint referencing ProductSubcategory.ProductSubcategoryID.
FK_Product_ProductModel_ProductModelID	ProductModelID	PK_ProductModel_ProductModelID (Production.ProductModel)	Foreign key constraint referencing ProductModel.ProductModelID.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.ProductCostHistory	FK_ProductCostHistory_Product_ProductID	PK_Product_ProductID
Production.ProductDocument	FK_ProductDocument_Product_ProductID	PK_Product_ProductID
Production.ProductInventory	FK_ProductInventory_Product_ProductID	PK_Product_ProductID
Production.ProductListPriceHistory	FK_ProductListPriceHistory_Product_ProductID	PK_Product_ProductID
Production.ProductProductPhoto	FK_ProductProductPhoto_Product_ProductID	PK_Product_ProductID
Production.ProductReview	FK_ProductReview_Product_ProductID	PK_Product_ProductID
Purchasing.ProductVendor	FK_ProductVendor_Product_ProductID	PK_Product_ProductID
Sales.SpecialOfferProduct	FK_SpecialOfferProduct_Product_ProductID	PK_Product_ProductID
Production.TransactionHistory	FK_TransactionHistory_Product_ProductID	PK_Product_ProductID
Production.WorkOrder	FK_WorkOrder_Product_ProductID	PK_Product_ProductID
Production.BillOfMaterials	FK_BillOfMaterials_Product_ComponentID	PK_Product_ProductID
Purchasing.PurchaseOrderDetail	FK_PurchaseOrderDetail_Product_ProductID	PK_Product_ProductID
Sales.ShoppingCartItem	FK_ShoppingCartItem_Product_ProductID	PK_Product_ProductID
Production.BillOfMaterials	FK_BillOfMaterials_Product_ProductAssemblyID	PK_Product_ProductID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_Product_Name	Name	ASC	Yes	NONCLUSTERED
AK_Product_ProductNumber	ProductNumber	ASC	Yes	NONCLUSTERED
AK_Product_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_Product_ProductID	ProductID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_Product_SafetyStockLevel	SafetyStockLevel	([SafetyStockLevel]>(0))
CK_Product_ReorderPoint	ReorderPoint	([ReorderPoint]>(0))
CK_Product_StandardCost	StandardCost	([StandardCost]>=(0.00))
CK_Product_ListPrice	ListPrice	([ListPrice]>=(0.00))
CK_Product_Weight	Weight	([Weight]>(0.00))
CK_Product_DaysToManufacture	DaysToManufacture	([DaysToManufacture]>=(0))
CK_Product_ProductLine	ProductLine	(upper([ProductLine])='R' OR upper([ProductLine])='M' OR upper([ProductLine])='T' OR upper([ProductLine])='S' OR [ProductLine] IS NULL)
CK_Product_Class	Class	(upper([Class])='H' OR upper([Class])='M' OR upper([Class])='L' OR [Class] IS NULL)
CK_Product_Style	Style	(upper([Style])='U' OR upper([Style])='M' OR upper([Style])='W' OR [Style] IS NULL)
CK_Product_SellEndDate	SellStartDate	([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)
CK_Product_SellEndDate	SellEndDate	([SellEndDate]>=[SellStartDate] OR [SellEndDate] IS NULL)

Objects that depend on Production.Product

dbo.ufnGetProductDealerPrice
dbo.ufnGetProductListPrice
dbo.ufnGetProductStandardCost
dbo.uspGetBillOfMaterials
dbo.uspGetWhereUsedProductID
Production.vProductAndDescription

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[Product](
    [ProductID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ProductNumber] [nvarchar](25) COLLATE Latin1_General_CS_AS NOT NULL,
    [MakeFlag] [dbo].[Flag] NOT NULL,
    [FinishedGoodsFlag] [dbo].[Flag] NOT NULL,
    [Color] [nvarchar](15) COLLATE Latin1_General_CS_AS NULL,
    [SafetyStockLevel] [smallint] NOT NULL,
    [ReorderPoint] [smallint] NOT NULL,
    [StandardCost] [money] NOT NULL,
    [ListPrice] [money] NOT NULL,
    [Size] [nvarchar](5) COLLATE Latin1_General_CS_AS NULL,
    [SizeUnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NULL,
    [WeightUnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NULL,
    [Weight] [decimal](8, 2) NULL,
    [DaysToManufacture] [int] NOT NULL,
    [ProductLine] [nchar](2) COLLATE Latin1_General_CS_AS NULL,
    [Class] [nchar](2) COLLATE Latin1_General_CS_AS NULL,
    [Style] [nchar](2) COLLATE Latin1_General_CS_AS NULL,
    [ProductSubcategoryID] [int] NULL,
    [ProductModelID] [int] NULL,
    [SellStartDate] [datetime] NOT NULL,
    [SellEndDate] [datetime] NULL,
    [DiscontinuedDate] [datetime] NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductCategory

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductCategoryID	int			Primary key for ProductCategory records.
	Name	Name			Category description.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.ProductSubcategory	FK_ProductSubcategory_ProductCategory_ProductCategoryID	PK_ProductCategory_ProductCategoryID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_ProductCategory_Name	Name	ASC	Yes	NONCLUSTERED
AK_ProductCategory_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ProductCategory_ProductCategoryID	ProductCategoryID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductCategory](
    [ProductCategoryID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductCostHistory

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductID	int			Product identification number. Foreign key to Product.ProductID
	StartDate	datetime			Product cost start date.
	EndDate	datetime	Yes		Product cost end date.
	StandardCost	money			Standard cost of the product.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductCostHistory_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ProductCostHistory_ProductID_StartDate	ProductID	ASC	Yes	CLUSTERED
PK_ProductCostHistory_ProductID_StartDate	StartDate	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_ProductCostHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_ProductCostHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_ProductCostHistory_StandardCost	StandardCost	([StandardCost]>=(0.00))

Objects that depend on Production.ProductCostHistory

dbo.ufnGetProductStandardCost

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductCostHistory](
    [ProductID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [StandardCost] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductDescription

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductDescriptionID	int			Primary key for ProductDescription records.
	Description	nvarchar (400)			Description of the product.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.ProductModelProductDescriptionCulture	FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID	PK_ProductDescription_ProductDescriptionID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_ProductDescription_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ProductDescription_ProductDescriptionID	ProductDescriptionID	ASC	Yes	CLUSTERED

Objects that depend on Production.ProductDescription

Production.vProductAndDescription

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductDescription](
    [ProductDescriptionID] [int] IDENTITY(1,1) NOT NULL,
    [Description] [nvarchar](400) COLLATE Latin1_General_CS_AS NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductDocument

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductID	int			Product identification number. Foreign key to Product.ProductID.
	DocumentID	int			Document identification number. Foreign key to Document.DocumentID.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductDocument_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.
FK_ProductDocument_Document_DocumentID	DocumentID	PK_Document_DocumentID (Production.Document)	Foreign key constraint referencing Document.DocumentID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ProductDocument_ProductID_DocumentID	ProductID	ASC	Yes	CLUSTERED
PK_ProductDocument_ProductID_DocumentID	DocumentID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductDocument](
    [ProductID] [int] NOT NULL,
    [DocumentID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductInventory

Fields

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

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductInventory_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.
FK_ProductInventory_Location_LocationID	LocationID	PK_Location_LocationID (Production.Location)	Foreign key constraint referencing Location.LocationID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ProductInventory_ProductID_LocationID	ProductID	ASC	Yes	CLUSTERED
PK_ProductInventory_ProductID_LocationID	LocationID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_ProductInventory_Shelf	Shelf	([Shelf] like '[A-Za-z]' OR [Shelf]='N/A')
CK_ProductInventory_Bin	Bin	([Bin]>=(0) AND [Bin]<=(100))

Objects that depend on Production.ProductInventory

dbo.ufnGetStock

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductInventory](
    [ProductID] [int] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [Shelf] [nvarchar](10) COLLATE Latin1_General_CS_AS NOT NULL,
    [Bin] [tinyint] NOT NULL,
    [Quantity] [smallint] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductListPriceHistory

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductID	int			Product identification number. Foreign key to Product.ProductID
	StartDate	datetime			List price start date.
	EndDate	datetime	Yes		List price end date
	ListPrice	money			Product list price.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductListPriceHistory_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ProductListPriceHistory_ProductID_StartDate	ProductID	ASC	Yes	CLUSTERED
PK_ProductListPriceHistory_ProductID_StartDate	StartDate	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_ProductListPriceHistory_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_ProductListPriceHistory_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_ProductListPriceHistory_ListPrice	ListPrice	([ListPrice]>(0.00))

Objects that depend on Production.ProductListPriceHistory

dbo.ufnGetProductDealerPrice
dbo.ufnGetProductListPrice

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductListPriceHistory](
    [ProductID] [int] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [ListPrice] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductModel

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductModelID	int			Primary key for ProductModel records.
	Name	Name			Product model description.
	CatalogDescription	xml	Yes		Detailed product catalog information in xml format.
	Instructions	xml	Yes		Manufacturing instructions in xml format.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.Product	FK_Product_ProductModel_ProductModelID	PK_ProductModel_ProductModelID
Production.ProductModelIllustration	FK_ProductModelIllustration_ProductModel_ProductModelID	PK_ProductModel_ProductModelID
Production.ProductModelProductDescriptionCulture	FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID	PK_ProductModel_ProductModelID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_ProductModel_Name	Name	ASC	Yes	NONCLUSTERED
AK_ProductModel_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ProductModel_ProductModelID	ProductModelID	ASC	Yes	CLUSTERED
PXML_ProductModel_CatalogDescription	CatalogDescription	ASC		XML
PXML_ProductModel_Instructions	Instructions	ASC		XML

Objects that depend on Production.ProductModel

Production.vProductAndDescription
Production.vProductModelCatalogDescription
Production.vProductModelInstructions

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductModel](
    [ProductModelID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [CatalogDescription] [xml](CONTENT [Production].[ProductDescriptionSchemaCollection]) NULL,
    [Instructions] [xml](CONTENT [Production].[ManuInstructionsSchemaCollection]) NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductModelIllustration

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductModelID	int			Primary key. Foreign key to ProductModel.ProductModelID.
	IllustrationID	int			Primary key. Foreign key to Illustration.IllustrationID.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductModelIllustration_ProductModel_ProductModelID	ProductModelID	PK_ProductModel_ProductModelID (Production.ProductModel)	Foreign key constraint referencing ProductModel.ProductModelID.
FK_ProductModelIllustration_Illustration_IllustrationID	IllustrationID	PK_Illustration_IllustrationID (Production.Illustration)	Foreign key constraint referencing Illustration.IllustrationID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ProductModelIllustration_ProductModelID_IllustrationID	ProductModelID	ASC	Yes	CLUSTERED
PK_ProductModelIllustration_ProductModelID_IllustrationID	IllustrationID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductModelIllustration](
    [ProductModelID] [int] NOT NULL,
    [IllustrationID] [int] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductModelProductDescriptionCulture

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductModelID	int			Primary key. Foreign key to ProductModel.ProductModelID.
	ProductDescriptionID	int			Primary key. Foreign key to ProductDescription.ProductDescriptionID.
	CultureID	nchar (6)			Culture identification number. Foreign key to Culture.CultureID.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductModelProductDescriptionCulture_ProductModel_ProductModelID	ProductModelID	PK_ProductModel_ProductModelID (Production.ProductModel)	Foreign key constraint referencing ProductModel.ProductModelID.
FK_ProductModelProductDescriptionCulture_ProductDescription_ProductDescriptionID	ProductDescriptionID	PK_ProductDescription_ProductDescriptionID (Production.ProductDescription)	Foreign key constraint referencing ProductDescription.ProductDescriptionID.
FK_ProductModelProductDescriptionCulture_Culture_CultureID	CultureID	PK_Culture_CultureID (Production.Culture)	Foreign key constraint referencing Culture.CultureID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	ProductModelID	ASC	Yes	CLUSTERED
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	ProductDescriptionID	ASC	Yes	CLUSTERED
PK_ProductModelProductDescriptionCulture_ProductModelID_ProductDescriptionID_CultureID	CultureID	ASC	Yes	CLUSTERED

Objects that depend on Production.ProductModelProductDescriptionCulture

Production.vProductAndDescription

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductModelProductDescriptionCulture](
    [ProductModelID] [int] NOT NULL,
    [ProductDescriptionID] [int] NOT NULL,
    [CultureID] [nchar](6) COLLATE Latin1_General_CS_AS NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductPhoto

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductPhotoID	int			Primary key for ProductPhoto records.
	ThumbNailPhoto	varbinary	Yes		Small image of the product.
	ThumbnailPhotoFileName	nvarchar (50)	Yes		Small image file name.
	LargePhoto	varbinary	Yes		Large image of the product.
	LargePhotoFileName	nvarchar (50)	Yes		Large image file name.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.ProductProductPhoto	FK_ProductProductPhoto_ProductPhoto_ProductPhotoID	PK_ProductPhoto_ProductPhotoID

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ProductPhoto_ProductPhotoID	ProductPhotoID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductPhoto](
    [ProductPhotoID] [int] IDENTITY(1,1) NOT NULL,
    [ThumbNailPhoto] [varbinary](max) NULL,
    [ThumbnailPhotoFileName] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL,
    [LargePhoto] [varbinary](max) NULL,
    [LargePhotoFileName] [nvarchar](50) COLLATE Latin1_General_CS_AS NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductProductPhoto

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductID	int			Product identification number. Foreign key to Product.ProductID.
	ProductPhotoID	int			Product photo identification number. Foreign key to ProductPhoto.ProductPhotoID.
	Primary	Flag		((0))	0 = Photo is not the principal image. 1 = Photo is the principal image.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductProductPhoto_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.
FK_ProductProductPhoto_ProductPhoto_ProductPhotoID	ProductPhotoID	PK_ProductPhoto_ProductPhotoID (Production.ProductPhoto)	Foreign key constraint referencing ProductPhoto.ProductPhotoID.

Indices

Index name	Column name	Sort direction	Unique	Index type
PK_ProductProductPhoto_ProductID_ProductPhotoID	ProductID	ASC	Yes	NONCLUSTERED
PK_ProductProductPhoto_ProductID_ProductPhotoID	ProductPhotoID	ASC	Yes	NONCLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductProductPhoto](
    [ProductID] [int] NOT NULL,
    [ProductPhotoID] [int] NOT NULL,
    [Primary] [dbo].[Flag] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductReview

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductReviewID	int			Primary key for ProductReview records.
	ProductID	int			Product identification number. Foreign key to Product.ProductID.
	ReviewerName	Name			Name of the reviewer.
	ReviewDate	datetime		(getdate())	Date review was submitted.
	EmailAddress	nvarchar (50)			Reviewer's e-mail address.
	Rating	int			Product rating given by the reviewer. Scale is 1 to 5 with 5 as the highest rating.
	Comments	nvarchar (3850)	Yes		Reviewer's comments
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductReview_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_ProductReview_ProductID_Name	ProductID	ASC		NONCLUSTERED
IX_ProductReview_ProductID_Name	ReviewerName	ASC		NONCLUSTERED
PK_ProductReview_ProductReviewID	ProductReviewID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_ProductReview_Rating	Rating	([Rating]>=(1) AND [Rating]<=(5))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductReview](
    [ProductReviewID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReviewerName] [dbo].[Name] NOT NULL,
    [ReviewDate] [datetime] NOT NULL,
    [EmailAddress] [nvarchar](50) COLLATE Latin1_General_CS_AS NOT NULL,
    [Rating] [int] NOT NULL,
    [Comments] [nvarchar](3850) COLLATE Latin1_General_CS_AS NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ProductSubcategory

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductSubcategoryID	int			Primary key for ProductSubcategory records.
	ProductCategoryID	int			Product category identification number. Foreign key to ProductCategory.ProductCategoryID.
	Name	Name			Subcategory description.
	rowguid	uniqueidentifier		(newid())	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductSubcategory_ProductCategory_ProductCategoryID	ProductCategoryID	PK_ProductCategory_ProductCategoryID (Production.ProductCategory)	Foreign key constraint referencing ProductCategory.ProductCategoryID.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.Product	FK_Product_ProductSubcategory_ProductSubcategoryID	PK_ProductSubcategory_ProductSubcategoryID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_ProductSubcategory_Name	Name	ASC	Yes	NONCLUSTERED
AK_ProductSubcategory_rowguid	rowguid	ASC	Yes	NONCLUSTERED
PK_ProductSubcategory_ProductSubcategoryID	ProductSubcategoryID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ProductSubcategory](
    [ProductSubcategoryID] [int] IDENTITY(1,1) NOT NULL,
    [ProductCategoryID] [int] NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.ScrapReason

Fields

	Field name	Data type	Nullable	Default value	Field description
	ScrapReasonID	smallint			Primary key for ScrapReason records.
	Name	Name			Failure description.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.WorkOrder	FK_WorkOrder_ScrapReason_ScrapReasonID	PK_ScrapReason_ScrapReasonID

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_ScrapReason_Name	Name	ASC	Yes	NONCLUSTERED
PK_ScrapReason_ScrapReasonID	ScrapReasonID	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[ScrapReason](
    [ScrapReasonID] [smallint] IDENTITY(1,1) NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.TransactionHistory

Fields

	Field name	Data type	Nullable	Default value	Field description
	TransactionID	int			Primary key for TransactionHistory records.
	ProductID	int			Product identification number. Foreign key to Product.ProductID.
	ReferenceOrderID	int			Purchase order, sales order, or work order identification number.
	ReferenceOrderLineID	int		((0))	Line number associated with the purchase order, sales order, or work order.
	TransactionDate	datetime		(getdate())	Date and time of the transaction.
	TransactionType	nchar (1)			W = WorkOrder, S = SalesOrder, P = PurchaseOrder
	Quantity	int			Product quantity.
	ActualCost	money			Product cost.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_TransactionHistory_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_TransactionHistory_ProductID	ProductID	ASC		NONCLUSTERED
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderID	ASC		NONCLUSTERED
IX_TransactionHistory_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderLineID	ASC		NONCLUSTERED
PK_TransactionHistory_TransactionID	TransactionID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_TransactionHistory_TransactionType	TransactionType	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

Objects that depend on Production.TransactionHistory

Production.iWorkOrder
Production.uWorkOrder
Purchasing.iPurchaseOrderDetail
Purchasing.uPurchaseOrderDetail
Sales.iduSalesOrderDetail

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistory](
    [TransactionID] [int] IDENTITY(100000,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineID] [int] NOT NULL,
    [TransactionDate] [datetime] NOT NULL,
    [TransactionType] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.TransactionHistoryArchive

Fields

	Field name	Data type	Nullable	Default value	Field description
	TransactionID	int			Primary key for TransactionHistoryArchive records.
	ProductID	int			Product identification number. Foreign key to Product.ProductID.
	ReferenceOrderID	int			Purchase order, sales order, or work order identification number.
	ReferenceOrderLineID	int		((0))	Line number associated with the purchase order, sales order, or work order.
	TransactionDate	datetime		(getdate())	Date and time of the transaction.
	TransactionType	nchar (1)			W = Work Order, S = Sales Order, P = Purchase Order
	Quantity	int			Product quantity.
	ActualCost	money			Product cost.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_TransactionHistoryArchive_ProductID	ProductID	ASC		NONCLUSTERED
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderID	ASC		NONCLUSTERED
IX_TransactionHistoryArchive_ReferenceOrderID_ReferenceOrderLineID	ReferenceOrderLineID	ASC		NONCLUSTERED
PK_TransactionHistoryArchive_TransactionID	TransactionID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_TransactionHistoryArchive_TransactionType	TransactionType	(upper([TransactionType])='P' OR upper([TransactionType])='S' OR upper([TransactionType])='W')

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[TransactionHistoryArchive](
    [TransactionID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [ReferenceOrderID] [int] NOT NULL,
    [ReferenceOrderLineID] [int] NOT NULL,
    [TransactionDate] [datetime] NOT NULL,
    [TransactionType] [nchar](1) COLLATE Latin1_General_CS_AS NOT NULL,
    [Quantity] [int] NOT NULL,
    [ActualCost] [money] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.UnitMeasure

Fields

	Field name	Data type	Nullable	Default value	Field description
	UnitMeasureCode	nchar (3)			Primary key.
	Name	Name			Unit of measure description.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.Product	FK_Product_UnitMeasure_SizeUnitMeasureCode	PK_UnitMeasure_UnitMeasureCode
Production.Product	FK_Product_UnitMeasure_WeightUnitMeasureCode	PK_UnitMeasure_UnitMeasureCode
Purchasing.ProductVendor	FK_ProductVendor_UnitMeasure_UnitMeasureCode	PK_UnitMeasure_UnitMeasureCode
Production.BillOfMaterials	FK_BillOfMaterials_UnitMeasure_UnitMeasureCode	PK_UnitMeasure_UnitMeasureCode

Indices

Index name	Column name	Sort direction	Unique	Index type
AK_UnitMeasure_Name	Name	ASC	Yes	NONCLUSTERED
PK_UnitMeasure_UnitMeasureCode	UnitMeasureCode	ASC	Yes	CLUSTERED

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[UnitMeasure](
    [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [Name] [dbo].[Name] NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.WorkOrder

Fields

	Field name	Data type	Nullable	Default value	Field description
	WorkOrderID	int			Primary key for WorkOrder records.
	ProductID	int			Product identification number. Foreign key to Product.ProductID.
	OrderQty	int			Product quantity to build.
	StockedQty	int			Quantity built and put in inventory.
	ScrappedQty	smallint			Quantity that failed inspection.
	StartDate	datetime			Work order start date.
	EndDate	datetime	Yes		Work order end date.
	DueDate	datetime			Work order due date.
	ScrapReasonID	smallint	Yes		Reason for inspection failure.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_WorkOrder_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.
FK_WorkOrder_ScrapReason_ScrapReasonID	ScrapReasonID	PK_ScrapReason_ScrapReasonID (Production.ScrapReason)	Foreign key constraint referencing ScrapReason.ScrapReasonID.

Referencing tables

Table name	Foreign key	Primary key or unique constraint
Production.WorkOrderRouting	FK_WorkOrderRouting_WorkOrder_WorkOrderID	PK_WorkOrder_WorkOrderID

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_WorkOrder_ProductID	ProductID	ASC		NONCLUSTERED
IX_WorkOrder_ScrapReasonID	ScrapReasonID	ASC		NONCLUSTERED
PK_WorkOrder_WorkOrderID	WorkOrderID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_WorkOrder_OrderQty	OrderQty	([OrderQty]>(0))
CK_WorkOrder_ScrappedQty	ScrappedQty	([ScrappedQty]>=(0))
CK_WorkOrder_EndDate	StartDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)
CK_WorkOrder_EndDate	EndDate	([EndDate]>=[StartDate] OR [EndDate] IS NULL)

Triggers

Trigger name:	Production.iWorkOrder
Description:	AFTER INSERT trigger that inserts a row in the TransactionHistory table.
Creation date:	26 Apr 2006
Trigger type:	INSERT
Trigger active:	Yes

CREATE TRIGGER [Production].[iWorkOrder] ON [Production].[WorkOrder] 
AFTER INSERT AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO [Production].[TransactionHistory](
            [ProductID]
            ,[ReferenceOrderID]
            ,[TransactionType]
            ,[TransactionDate]
            ,[Quantity]
            ,[ActualCost])
        SELECT 
            inserted.[ProductID]
            ,inserted.[WorkOrderID]
            ,'W'
            ,GETDATE()
            ,inserted.[OrderQty]
            ,0
        FROM inserted;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Trigger name:	Production.uWorkOrder
Description:	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in the WorkOrder table.
Creation date:	26 Apr 2006
Trigger type:	UPDATE
Trigger active:	Yes

CREATE TRIGGER [Production].[uWorkOrder] ON [Production].[WorkOrder] 
AFTER UPDATE AS 
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        IF UPDATE([ProductID]) OR UPDATE([OrderQty])
        BEGIN
            INSERT INTO [Production].[TransactionHistory](
                [ProductID]
                ,[ReferenceOrderID]
                ,[TransactionType]
                ,[TransactionDate]
                ,[Quantity])
            SELECT 
                inserted.[ProductID]
                ,inserted.[WorkOrderID]
                ,'W'
                ,GETDATE()
                ,inserted.[OrderQty]
            FROM inserted;
        END;
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;

Objects that depend on Production.WorkOrder

Production.uWorkOrder
Production.WorkOrder

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[WorkOrder](
    [WorkOrderID] [int] IDENTITY(1,1) NOT NULL,
    [ProductID] [int] NOT NULL,
    [OrderQty] [int] NOT NULL,
    [StockedQty]  AS (isnull([OrderQty]-[ScrappedQty],(0))),
    [ScrappedQty] [smallint] NOT NULL,
    [StartDate] [datetime] NOT NULL,
    [EndDate] [datetime] NULL,
    [DueDate] [datetime] NOT NULL,
    [ScrapReasonID] [smallint] NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Production.WorkOrderRouting

Fields

	Field name	Data type	Nullable	Default value	Field description
	WorkOrderID	int			Primary key. Foreign key to WorkOrder.WorkOrderID.
	ProductID	int			Primary key. Foreign key to Product.ProductID.
	OperationSequence	smallint			Primary key. Indicates the manufacturing process sequence.
	LocationID	smallint			Manufacturing location where the part is processed. Foreign key to Location.LocationID.
	ScheduledStartDate	datetime			Planned manufacturing start date.
	ScheduledEndDate	datetime			Planned manufacturing end date.
	ActualStartDate	datetime	Yes		Actual start date.
	ActualEndDate	datetime	Yes		Actual end date.
	ActualResourceHrs	decimal (9.4)	Yes		Number of manufacturing hours used.
	PlannedCost	money			Estimated manufacturing cost.
	ActualCost	money	Yes		Actual manufacturing cost.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_WorkOrderRouting_WorkOrder_WorkOrderID	WorkOrderID	PK_WorkOrder_WorkOrderID (Production.WorkOrder)	Foreign key constraint referencing WorkOrder.WorkOrderID.
FK_WorkOrderRouting_Location_LocationID	LocationID	PK_Location_LocationID (Production.Location)	Foreign key constraint referencing Location.LocationID.

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_WorkOrderRouting_ProductID	ProductID	ASC		NONCLUSTERED
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	WorkOrderID	ASC	Yes	CLUSTERED
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	ProductID	ASC	Yes	CLUSTERED
PK_WorkOrderRouting_WorkOrderID_ProductID_OperationSequence	OperationSequence	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_WorkOrderRouting_ScheduledEndDate	ScheduledStartDate	([ScheduledEndDate]>=[ScheduledStartDate])
CK_WorkOrderRouting_ScheduledEndDate	ScheduledEndDate	([ScheduledEndDate]>=[ScheduledStartDate])
CK_WorkOrderRouting_ActualEndDate	ActualStartDate	([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)
CK_WorkOrderRouting_ActualEndDate	ActualEndDate	([ActualEndDate]>=[ActualStartDate] OR [ActualEndDate] IS NULL OR [ActualStartDate] IS NULL)
CK_WorkOrderRouting_ActualResourceHrs	ActualResourceHrs	([ActualResourceHrs]>=(0.0000))
CK_WorkOrderRouting_PlannedCost	PlannedCost	([PlannedCost]>(0.00))
CK_WorkOrderRouting_ActualCost	ActualCost	([ActualCost]>(0.00))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Production].[WorkOrderRouting](
    [WorkOrderID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [OperationSequence] [smallint] NOT NULL,
    [LocationID] [smallint] NOT NULL,
    [ScheduledStartDate] [datetime] NOT NULL,
    [ScheduledEndDate] [datetime] NOT NULL,
    [ActualStartDate] [datetime] NULL,
    [ActualEndDate] [datetime] NULL,
    [ActualResourceHrs] [decimal](9, 4) NULL,
    [PlannedCost] [money] NOT NULL,
    [ActualCost] [money] NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.ProductVendor

Fields

	Field name	Data type	Nullable	Default value	Field description
	ProductID	int			Primary key. Foreign key to Product.ProductID.
	VendorID	int			Primary key. Foreign key to Vendor.VendorID.
	AverageLeadTime	int			The average span of time (in days) between placing an order with the vendor and receiving the purchased product.
	StandardPrice	money			The vendor's usual selling price.
	LastReceiptCost	money	Yes		The selling price when last purchased.
	LastReceiptDate	datetime	Yes		Date the product was last received by the vendor.
	MinOrderQty	int			The maximum quantity that should be ordered.
	MaxOrderQty	int			The minimum quantity that should be ordered.
	OnOrderQty	int	Yes		The quantity currently on order.
	UnitMeasureCode	nchar (3)			The product's unit of measure.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_ProductVendor_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.
FK_ProductVendor_Vendor_VendorID	VendorID	PK_Vendor_VendorID (Purchasing.Vendor)	Foreign key constraint referencing Vendor.VendorID.
FK_ProductVendor_UnitMeasure_UnitMeasureCode	UnitMeasureCode	PK_UnitMeasure_UnitMeasureCode (Production.UnitMeasure)	Foreign key constraint referencing UnitMeasure.UnitMeasureCode.

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_ProductVendor_UnitMeasureCode	UnitMeasureCode	ASC		NONCLUSTERED
IX_ProductVendor_VendorID	VendorID	ASC		NONCLUSTERED
PK_ProductVendor_ProductID_VendorID	ProductID	ASC	Yes	CLUSTERED
PK_ProductVendor_ProductID_VendorID	VendorID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_ProductVendor_AverageLeadTime	AverageLeadTime	([AverageLeadTime]>=(1))
CK_ProductVendor_StandardPrice	StandardPrice	([StandardPrice]>(0.00))
CK_ProductVendor_LastReceiptCost	LastReceiptCost	([LastReceiptCost]>(0.00))
CK_ProductVendor_MinOrderQty	MinOrderQty	([MinOrderQty]>=(1))
CK_ProductVendor_MaxOrderQty	MaxOrderQty	([MaxOrderQty]>=(1))
CK_ProductVendor_OnOrderQty	OnOrderQty	([OnOrderQty]>=(0))

Table definition

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[ProductVendor](
    [ProductID] [int] NOT NULL,
    [VendorID] [int] NOT NULL,
    [AverageLeadTime] [int] NOT NULL,
    [StandardPrice] [money] NOT NULL,
    [LastReceiptCost] [money] NULL,
    [LastReceiptDate] [datetime] NULL,
    [MinOrderQty] [int] NOT NULL,
    [MaxOrderQty] [int] NOT NULL,
    [OnOrderQty] [int] NULL,
    [UnitMeasureCode] [nchar](3) COLLATE Latin1_General_CS_AS NOT NULL,
    [ModifiedDate] [datetime] NOT NULL
) ON [PRIMARY]

Table: Purchasing.PurchaseOrderDetail

Fields

	Field name	Data type	Nullable	Default value	Field description
	PurchaseOrderID	int			Primary key. Foreign key to PurchaseOrderHeader.PurchaseOrderID.
	PurchaseOrderDetailID	int			Primary key. One line number per purchased product.
	DueDate	datetime			Date the product is expected to be received.
	OrderQty	smallint			Quantity ordered.
	ProductID	int			Product identification number. Foreign key to Product.ProductID.
	UnitPrice	money			Vendor's selling price of a single product.
	LineTotal	money			Per product subtotal. Computed as OrderQty * UnitPrice.
	ReceivedQty	decimal (8.2)			Quantity actually received from the vendor.
	RejectedQty	decimal (8.2)			Quantity rejected during inspection.
	StockedQty	decimal (9.2)			Quantity accepted into inventory. Computed as ReceivedQty - RejectedQty.
	ModifiedDate	datetime		(getdate())	Date and time the record was last updated.

Foreign keys dependencies:

Name	Column	Reference	Foreign Key description
FK_PurchaseOrderDetail_PurchaseOrderHeader_PurchaseOrderID	PurchaseOrderID	PK_PurchaseOrderHeader_PurchaseOrderID (Purchasing.PurchaseOrderHeader)	Foreign key constraint referencing PurchaseOrderHeader.PurchaseOrderID.
FK_PurchaseOrderDetail_Product_ProductID	ProductID	PK_Product_ProductID (Production.Product)	Foreign key constraint referencing Product.ProductID.

Indices

Index name	Column name	Sort direction	Unique	Index type
IX_PurchaseOrderDetail_ProductID	ProductID	ASC		NONCLUSTERED
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID	PurchaseOrderID	ASC	Yes	CLUSTERED
PK_PurchaseOrderDetail_PurchaseOrderID_PurchaseOrderDetailID	PurchaseOrderDetailID	ASC	Yes	CLUSTERED

Check constraints

Check name	Column name	Check expresion
CK_PurchaseOrderDetail_OrderQty	OrderQty	([OrderQty]>(0))
CK_PurchaseOrderDetail_UnitPrice	UnitPrice	([UnitPrice]>=(0.00))
CK_PurchaseOrderDetail_ReceivedQty	ReceivedQty	([ReceivedQty]>=(0.00))
CK_PurchaseOrderDetail_RejectedQty	RejectedQty	([RejectedQty]>=(0.00))

Triggers

Trigger name:	Purchasing.iPurchaseOrderDetail
Description:	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.
Creation date:	26 Apr 2006
Trigger type:	INSERT
Trigger active:	Yes

CREATE TRIGGER [Purchasing].[iPurchaseOrderDetail] ON [Purchasing].[PurchaseOrderDetail] 
AFTER INSERT AS
BEGIN
    DECLARE @Count int;

    SET @Count = @@ROWCOUNT;
    IF @Count = 0 
        RETURN;

    SET NOCOUNT ON;

    BEGIN TRY
        INSERT INTO [Production].[TransactionHistory]
            ([ProductID]
            ,[ReferenceOrderID]
            ,[ReferenceOrderLineID]
            ,[TransactionType]
            ,[TransactionDate]
            ,[Quantity]
            ,[ActualCost])
        SELECT 
            inserted.[ProductID]
            ,inserted.[PurchaseOrderID]
            ,inserted.[PurchaseOrderDetailID]
            ,'P'
            ,GETDATE()
            ,inserted.[OrderQty]
            ,inserted.[UnitPrice]
        FROM inserted 
            INNER JOIN [Purchasing].[PurchaseOrderHeader] 
            ON inserted.[PurchaseOrderID] = [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID];

        -- Update SubTotal in PurchaseOrderHeader record. Note that this causes the 
        -- PurchaseOrderHeader trigger to fire which will update the RevisionNumber.
        UPDATE [Purchasing].[PurchaseOrderHeader]
        SET [Purchasing].[PurchaseOrderHeader].[SubTotal] = 
            (SELECT SUM([Purchasing].[PurchaseOrderDetail].[LineTotal])
                FROM [Purchasing].[PurchaseOrderDetail]
                WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] = [Purchasing].[PurchaseOrderDetail].[PurchaseOrderID])
        WHERE [Purchasing].[PurchaseOrderHeader].[PurchaseOrderID] IN (SELECT inserted.[PurchaseOrderID] FROM inserted);
    END TRY
    BEGIN CATCH
        EXECUTE [dbo].[uspPrintError];

        -- Rollback any active or uncommittable transactions before
        -- inserting information in the ErrorLog
        IF @@TRANCOUNT > 0
        BEGIN
            ROLLBACK TRANSACTION;
        END

        EXECUTE [dbo].[uspLogError];
    END CATCH;
END;