Database reference - AdventureWorks2008

AdventureWorks2008 -  tables -  [Purchasing].[PurchaseOrderHeader]

Description

General purchase order information. See PurchaseOrderDetail. 

Table properties

namevalue
name[Purchasing].[PurchaseOrderHeader]
createdMar 19 2009 9:09PM
modifiedMar 19 2009 9:10PM 
ansi nullson 
quoted identifieron 
row count4,012
Size of data336 kb
Size of indexes144 kb
Maximum size of a single row74 bytes

Columns

columndatatypelengthbytesdefaultnullsPKFKUQcomputedcomment
PurchaseOrderIDint identity(1,1)104 no  yes    no  Primary key. 
RevisionNumbertinyint31((0))no      no  Incremental number to track changes to the purchase order over time. 
Statustinyint31((1))no      no  Order current status. 1 = Pending; 2 = Approved; 3 = Rejected; 4 = Complete 
EmployeeIDint104 no    Employee.BusinessEntityID      no  Employee who created the purchase order. Foreign key to Employee.BusinessEntityID. 
VendorIDint104 no    Vendor.BusinessEntityID      no  Vendor with whom the purchase order is placed. Foreign key to Vendor.BusinessEntityID. 
ShipMethodIDint104 no    ShipMethod.ShipMethodID      no  Shipping method. Foreign key to ShipMethod.ShipMethodID. 
OrderDatedatetime238(getdate())no      no  Purchase order creation date. 
ShipDatedatetime238 yes      no  Estimated shipment date from the vendor. 
SubTotalmoney198((0.00))no      no  Purchase order subtotal. Computed as SUM(PurchaseOrderDetail.LineTotal)for the appropriate PurchaseOrderID. 
TaxAmtmoney198((0.00))no      no  Tax amount. 
Freightmoney198((0.00))no      no  Shipping cost. 
TotalDuemoney198 no      yes  Total due to vendor. Computed as Subtotal + TaxAmt + Freight. 
ModifiedDatedatetime238(getdate())no      no  Date and time the record was last updated. 

Indexes

namedescriptioncolumncomment
IX_PurchaseOrderHeader_EmployeeIDnonclustered located on PRIMARYEmployeeIDNonclustered index. 
IX_PurchaseOrderHeader_VendorIDnonclustered located on PRIMARYVendorIDNonclustered index. 
PK_PurchaseOrderHeader_PurchaseOrderIDclustered, unique, primary key located on PRIMARYPurchaseOrderIDClustered index created by a primary key constraint. 

References

name
Employee
ShipMethod
Vendor

Referenced by

name
PurchaseOrderDetail

Foreign key graph

EmployeeShipMethodVendorPurchaseOrderHeaderPurchaseOrderDetail

Foreign keys

namecolumnsforeign columnstypecomment
FK_PurchaseOrderHeader_Employee_EmployeeIDEmployeeIDEmployee.BusinessEntityID   Foreign key constraint referencing Employee.EmployeeID. 
FK_PurchaseOrderHeader_Vendor_VendorIDVendorIDVendor.BusinessEntityID   Foreign key constraint referencing Vendor.VendorID. 
FK_PurchaseOrderHeader_ShipMethod_ShipMethodIDShipMethodIDShipMethod.ShipMethodID   Foreign key constraint referencing ShipMethod.ShipMethodID. 

Check constraints

namecolumncomment
CK_PurchaseOrderHeader_StatusStatus    Check constraint [Status] BETWEEN (1) AND (4) 
CK_PurchaseOrderHeader_ShipDateOrderDate  ShipDate    Check constraint [ShipDate] >= [OrderDate] OR [ShipDate] IS NULL 
CK_PurchaseOrderHeader_SubTotalSubTotal    Check constraint [SubTotal] >= (0.00) 
CK_PurchaseOrderHeader_TaxAmtTaxAmt    Check constraint [TaxAmt] >= (0.00) 
CK_PurchaseOrderHeader_FreightFreight    Check constraint [Freight] >= (0.00) 

Triggers

namecomment
uPurchaseOrderHeaderAFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table. 

Defaults

default namecolumncomment
DF_PurchaseOrderHeader_RevisionNumberRevisionNumberDefault constraint value of 0 
DF_PurchaseOrderHeader_StatusStatusDefault constraint value of 1 
DF_PurchaseOrderHeader_OrderDateOrderDateDefault constraint value of GETDATE() 
DF_PurchaseOrderHeader_SubTotalSubTotalDefault constraint value of 0.0 
DF_PurchaseOrderHeader_TaxAmtTaxAmtDefault constraint value of 0.0 
DF_PurchaseOrderHeader_FreightFreightDefault constraint value of 0.0 
DF_PurchaseOrderHeader_ModifiedDateModifiedDateDefault constraint value of GETDATE() 

Dependency graph

Objects that depend on [Purchasing].[PurchaseOrderHeader]

nameobject typedatabaseserverlevel
iPurchaseOrderDetailtriggerAdventureWorks2008SPRING\KATMAI1
uPurchaseOrderDetailtriggerAdventureWorks2008SPRING\KATMAI1
uPurchaseOrderHeadertriggerAdventureWorks2008SPRING\KATMAI1

Sample rows

PurchaseOrderIDRevisionNumberStatusEmployeeIDVendorIDShipMethodIDOrderDateShipDateSubTotalTaxAmtFreightTotalDueModifiedDate
1  0  4  258  1580  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  254  1496  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  257  1494  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  261  1650  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  251  1654  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  253  1664  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  255  1678  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  256  1616  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  259  1492  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  250  1602  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] ([BusinessEntityID])
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] ([BusinessEntityID])
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]



Documentation generated by SqlSpec