AdventureWorks - [Purchasing].[PurchaseOrderHeader]

Description

General purchase order information. See PurchaseOrderDetail.

Table properties

name	value
name	[Purchasing].[PurchaseOrderHeader]
created	Mar 19 2009 9:08PM
modified	Mar 19 2009 9:08PM
ansi nulls	on
quoted identifier	on
row count	4,012
Size of data	336 kb
Size of indexes	144 kb
Maximum size of a single row	74 bytes

Columns

column	datatype	length	bytes	default	nulls	PK	FK	computed	comment
PurchaseOrderID	int identity(1,1)	10	4		no	yes		no	Primary key.
RevisionNumber	tinyint	3	1	((0))	no			no	Incremental number to track changes to the purchase order over time.
Status	tinyint	3	1	((1))	no			no	Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete
EmployeeID	int	10	4		no		Employee.EmployeeID	no	Employee who created the purchase order. Foreign key to Employee.EmployeeID.
VendorID	int	10	4		no		Vendor.VendorID	no	Vendor with whom the purchase order is placed. Foreign key to Vendor.VendorID.
ShipMethodID	int	10	4		no		ShipMethod.ShipMethodID	no	Shipping method. Foreign key to ShipMethod.ShipMethodID.
OrderDate	datetime	23	8	(getdate())	no			no	Purchase order creation date.
ShipDate	datetime	23	8		yes			no	Estimated shipment date from the vendor.
SubTotal	money	19	8	((0.00))	no			no	Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID.
TaxAmt	money	19	8	((0.00))	no			no	Tax amount.
Freight	money	19	8	((0.00))	no			no	Shipping cost.
TotalDue	money	19	8		no			yes	Total due to vendor. Computed as Subtotal + TaxAmt + Freight.
ModifiedDate	datetime	23	8	(getdate())	no			no	Date and time the record was last updated.

Indexes

name	description	column	comment
IX_PurchaseOrderHeader_EmployeeID	nonclustered located on PRIMARY	EmployeeID	Nonclustered index.
IX_PurchaseOrderHeader_VendorID	nonclustered located on PRIMARY	VendorID	Nonclustered index.
PK_PurchaseOrderHeader_PurchaseOrderID	clustered, unique, primary key located on PRIMARY	PurchaseOrderID	Clustered index created by a primary key constraint.

References

name
Employee
ShipMethod
Vendor

Referenced by

name
PurchaseOrderDetail

Foreign key graph

EmployeeShipMethodVendorPurchaseOrderHeader PurchaseOrderDetail

Foreign keys

name	columns	foreign columns	comment
FK_PurchaseOrderHeader_Vendor_VendorID	VendorID	Vendor.VendorID	Foreign key constraint referencing Vendor.VendorID.
FK_PurchaseOrderHeader_Employee_EmployeeID	EmployeeID	Employee.EmployeeID	Foreign key constraint referencing Employee.EmployeeID.
FK_PurchaseOrderHeader_ShipMethod_ShipMethodID	ShipMethodID	ShipMethod.ShipMethodID	Foreign key constraint referencing ShipMethod.ShipMethodID.

Check constraints

name	column	comment
CK_PurchaseOrderHeader_Status ([Status]>=(1) AND [Status]<=(4))	Status	Check constraint [Status] BETWEEN (1) AND (4)
CK_PurchaseOrderHeader_ShipDate ([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL)	OrderDate ShipDate	Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL
CK_PurchaseOrderHeader_SubTotal ([SubTotal]>=(0.00))	SubTotal	Check constraint [SubTotal] >= (0.00)
CK_PurchaseOrderHeader_TaxAmt ([TaxAmt]>=(0.00))	TaxAmt	Check constraint [TaxAmt] >= (0.00)
CK_PurchaseOrderHeader_Freight ([Freight]>=(0.00))	Freight	Check constraint [Freight] >= (0.00)

Triggers

name	comment
uPurchaseOrderHeader	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.

Defaults

default name	column	comment
DF_PurchaseOrderHeader_RevisionNumber	RevisionNumber	Default constraint value of 0
DF_PurchaseOrderHeader_Status	Status	Default constraint value of 1
DF_PurchaseOrderHeader_OrderDate	OrderDate	Default constraint value of GETDATE()
DF_PurchaseOrderHeader_SubTotal	SubTotal	Default constraint value of 0.0
DF_PurchaseOrderHeader_TaxAmt	TaxAmt	Default constraint value of 0.0
DF_PurchaseOrderHeader_Freight	Freight	Default constraint value of 0.0
DF_PurchaseOrderHeader_ModifiedDate	ModifiedDate	Default constraint value of GETDATE()

Dependency graph

Objects that depend on [Purchasing].[PurchaseOrderHeader]

name	object type	level
iPurchaseOrderDetail	trigger	1
uPurchaseOrderDetail	trigger	1
uPurchaseOrderHeader	trigger	1

Sample rows

PurchaseOrderID	RevisionNumber	Status	EmployeeID	VendorID	ShipMethodID	OrderDate	ShipDate	SubTotal	TaxAmt	Freight	TotalDue	ModifiedDate
1	0	4	244	83	3	5/17/2001 12:00:00 AM	5/26/2001 12:00:00 AM	201.0400	16.0832	5.0260	222.1492	5/26/2001 12:00:00 AM
2	0	1	231	32	5	5/17/2001 12:00:00 AM	5/26/2001 12:00:00 AM	272.1015	21.7681	6.8025	300.6721	5/26/2001 12:00:00 AM
3	0	4	241	38	2	5/17/2001 12:00:00 AM	5/26/2001 12:00:00 AM	8847.3000	707.7840	221.1825	9776.2665	5/26/2001 12:00:00 AM
4	0	3	266	85	5	5/17/2001 12:00:00 AM	5/26/2001 12:00:00 AM	171.0765	13.6861	4.2769	189.0395	5/26/2001 12:00:00 AM
5	0	4	164	92	4	5/31/2001 12:00:00 AM	6/9/2001 12:00:00 AM	20397.3000	1631.7840	509.9325	22539.0165	6/9/2001 12:00:00 AM
6	0	4	223	11	3	5/31/2001 12:00:00 AM	6/9/2001 12:00:00 AM	14628.0750	1170.2460	365.7019	16164.0229	6/9/2001 12:00:00 AM
7	0	4	233	84	3	5/31/2001 12:00:00 AM	6/9/2001 12:00:00 AM	58685.5500	4694.8440	1467.1388	64847.5328	6/9/2001 12:00:00 AM
8	0	4	238	78	5	5/31/2001 12:00:00 AM	6/9/2001 12:00:00 AM	693.3780	55.4702	17.3345	766.1827	6/9/2001 12:00:00 AM
9	1	4	261	74	5	1/14/2002 12:00:00 AM	1/23/2002 12:00:00 AM	694.1655	55.5332	17.3541	767.0528	1/23/2002 12:00:00 AM
10	0	4	274	13	5	1/14/2002 12:00:00 AM	1/23/2002 12:00:00 AM	1796.0355	143.6828	44.9009	1984.6192	1/23/2002 12:00:00 AM

Code

CREATE TABLE [Purchasing].[PurchaseOrderHeader](
    [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL,
    [RevisionNumber] [tinyint] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [EmployeeID] [int] NOT NULL,
    [PurchaseOrderID] [int] IDENTITY(1,1) NOT NULL,
    [RevisionNumber] [tinyint] NOT NULL,
    [Status] [tinyint] NOT NULL,
    [VendorID] [int] NOT NULL,
    [ShipMethodID] [int] NOT NULL,
    [OrderDate] [datetime] NOT NULL,
    [ShipDate] [datetime] NULL,
    [SubTotal] [money] NOT NULL,
    [TaxAmt] [money] NOT NULL,
    [Freight] [money] NOT NULL,
    [TotalDue]  AS (isnull(([SubTotal]+[TaxAmt])+[Freight],(0))) PERSISTED NOT NULL,
    [ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_PurchaseOrderHeader_PurchaseOrderID] PRIMARY KEY CLUSTERED 
(
    [PurchaseOrderID] 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 [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID] FOREIGN KEY([EmployeeID])
REFERENCES [HumanResources].[Employee] ([EmployeeID])
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [FK_PurchaseOrderHeader_Employee_EmployeeID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_PurchaseOrderHeader_ShipMethod_ShipMethodID] FOREIGN KEY([ShipMethodID])
REFERENCES [Purchasing].[ShipMethod] ([ShipMethodID])
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [FK_PurchaseOrderHeader_ShipMethod_ShipMethodID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [FK_PurchaseOrderHeader_Vendor_VendorID] FOREIGN KEY([VendorID])
REFERENCES [Purchasing].[Vendor] ([VendorID])
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [FK_PurchaseOrderHeader_Vendor_VendorID]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_Freight] CHECK  (([Freight]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_Freight]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_ShipDate] CHECK  (([ShipDate]>=[OrderDate] OR [ShipDate] IS NULL))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_ShipDate]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_Status] CHECK  (([Status]>=(1) AND [Status]<=(4)))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_Status]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_SubTotal] CHECK  (([SubTotal]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_SubTotal]
ALTER TABLE [Purchasing].[PurchaseOrderHeader]  WITH CHECK ADD  CONSTRAINT [CK_PurchaseOrderHeader_TaxAmt] CHECK  (([TaxAmt]>=(0.00)))
ALTER TABLE [Purchasing].[PurchaseOrderHeader] CHECK CONSTRAINT [CK_PurchaseOrderHeader_TaxAmt]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_RevisionNumber]  DEFAULT ((0)) FOR [RevisionNumber]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_Status]  DEFAULT ((1)) FOR [Status]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_OrderDate]  DEFAULT (getdate()) FOR [OrderDate]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_SubTotal]  DEFAULT ((0.00)) FOR [SubTotal]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_TaxAmt]  DEFAULT ((0.00)) FOR [TaxAmt]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_Freight]  DEFAULT ((0.00)) FOR [Freight]
ALTER TABLE [Purchasing].[PurchaseOrderHeader] ADD  CONSTRAINT [DF_PurchaseOrderHeader_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

AdventureWorks - tables - [Purchasing].[PurchaseOrderHeader]

Description

Table properties

Columns

Indexes

References

Referenced by

Foreign key graph

Foreign keys

Check constraints

Triggers

Defaults

Dependency graph

Objects that depend on [Purchasing].[PurchaseOrderHeader]

Sample rows

Code