Sales.SpecialOfferProduct Table

Description

Cross-reference table mapping products to special offer discounts.

Properties

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

Columns

Column Name	Description	Datatype	Length	Default
SpecialOfferID	Primary key for SpecialOfferProduct records.	Integer	4
ProductID	Product identification number. Foreign key to Product.ProductID.	Integer	4
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
AK_SpecialOfferProduct_rowguid	Unique nonclustered index. Used to support replication samples.
IX_SpecialOfferProduct_ProductID	Nonclustered index.
PK_SpecialOfferProduct_SpecialOfferID_ProductID	Primary key (clustered) constraint

Relationships

Relationship	Description
FK_SpecialOfferProduct_Product_ProductID	Foreign key constraint referencing Product.ProductID.
FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID	Foreign key constraint referencing SpecialOffer.SpecialOfferID.

Objects that depend on Sales.SpecialOfferProduct

	Database Object	Object Type	Description	Dep Level
	iduSalesOrderDetail	Trigger	AFTER INSERT, DELETE, UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in SalesOrderDetail and updates the SalesOrderHeader.SubTotal column.	2
	Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	1

Objects that Sales.SpecialOfferProduct depends on

Database Object	Object Type	Description	Dep Level
dbo.Flag	User Defined Data Type		2
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
Sales.SpecialOffer	Table	Sale discounts lookup table.	1
Production.UnitMeasure	Table	Unit of measure lookup table.	2

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Sales].[SpecialOfferProduct](
    [SpecialOfferID] [int] NOT NULL,
    [ProductID] [int] NOT NULL,
    [rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_SpecialOfferProduct_SpecialOfferID_ProductID] PRIMARY KEY CLUSTERED 
(
    [SpecialOfferID] ASC,
    [ProductID] 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 [Sales].[SpecialOfferProduct] ADD  CONSTRAINT [DF_SpecialOfferProduct_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Sales].[SpecialOfferProduct] ADD  CONSTRAINT [DF_SpecialOfferProduct_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]
ALTER TABLE [Sales].[SpecialOfferProduct]  WITH CHECK ADD  CONSTRAINT [FK_SpecialOfferProduct_Product_ProductID] FOREIGN KEY([ProductID])
REFERENCES [Production].[Product] ([ProductID])
ALTER TABLE [Sales].[SpecialOfferProduct] CHECK CONSTRAINT [FK_SpecialOfferProduct_Product_ProductID]
ALTER TABLE [Sales].[SpecialOfferProduct]  WITH CHECK ADD  CONSTRAINT [FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID] FOREIGN KEY([SpecialOfferID])
REFERENCES [Sales].[SpecialOffer] ([SpecialOfferID])
ALTER TABLE [Sales].[SpecialOfferProduct] CHECK CONSTRAINT [FK_SpecialOfferProduct_SpecialOffer_SpecialOfferID]

Related Objects

Sales Schema
AdventureWorks Database