Purchasing.ShipMethod Table

Description

Shipping company lookup table.

Properties

Creation Date	08/01/2010 08:40
File Group	PRIMARY
Text File Group
System Object
Published for Replication
Rows	5
Data Space Used	8.00 KB
Index Space Used	40.00 KB

Columns

Column Name	Description	Datatype	Length	Default
ShipMethodID	Primary key for ShipMethod records.	int	4
Name	Shipping company name.	dbo.Name	50
ShipBase	Minimum shipping charge.	money	8	((0.00))
ShipRate	Shipping charge per pound.	money	8	((0.00))
rowguid	ROWGUIDCOL number uniquely identifying the record. Used to support a merge replication sample.	uniqueidentifier	16	(newid())
ModifiedDate	Date and time the record was last updated.	datetime	4	(getdate())

Indexes

Index	Description	Primary	Unique
AK_ShipMethod_Name	Unique nonclustered index.
AK_ShipMethod_rowguid	Unique nonclustered index. Used to support replication samples.
PK_ShipMethod_ShipMethodID	Primary key (clustered) constraint

Check Constraints

Name	Description	Expression
CK_ShipMethod_ShipBase	Check constraint [ShipBase] > (0.00)	([ShipBase]>(0.00))
CK_ShipMethod_ShipRate	Check constraint [ShipRate] > (0.00)	([ShipRate]>(0.00))

Relationships

Relationship	Description
FK_PurchaseOrderHeader_ShipMethod_ShipMethodID	Foreign key constraint referencing ShipMethod.ShipMethodID.
FK_SalesOrderHeader_ShipMethod_ShipMethodID	Foreign key constraint referencing ShipMethod.ShipMethodID.

Objects that depend on Purchasing.ShipMethod

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.	3
iPurchaseOrderDetail	Trigger	AFTER INSERT trigger that inserts a row in the TransactionHistory table and updates the PurchaseOrderHeader.SubTotal column.	3
Purchasing.PurchaseOrderDetail	Table	Individual products associated with a specific purchase order. See PurchaseOrderHeader.	2
Purchasing.PurchaseOrderHeader	Table	General purchase order information. See PurchaseOrderDetail.	1
Sales.SalesOrderDetail	Table	Individual products associated with a specific sales order. See SalesOrderHeader.	2
Sales.SalesOrderHeader	Table	General sales order information.	1
Sales.SalesOrderHeaderSalesReason	Table	Cross-reference table mapping sales orders to sales reason codes.	2
uPurchaseOrderDetail	Trigger	AFTER UPDATE trigger that inserts a row in the TransactionHistory table, updates ModifiedDate in PurchaseOrderDetail and updates the PurchaseOrderHeader.SubTotal column.	3
uPurchaseOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the PurchaseOrderHeader table.	2
uSalesOrderHeader	Trigger	AFTER UPDATE trigger that updates the RevisionNumber and ModifiedDate columns in the SalesOrderHeader table.Updates the SalesYTD column in the SalesPerson and SalesTerritory tables.	2
Sales.vSalesPersonSalesByFiscalYears	View	Uses PIVOT to return aggregated sales information for each sales representative.	2

Objects that Purchasing.ShipMethod depends on

	Database Object	Object Type	Description	Dep Level
	dbo.Name	User Defined Data Type		1

SQL

SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
CREATE TABLE [Purchasing].[ShipMethod](
	[ShipMethodID] [int] IDENTITY(1,1) NOT NULL,
	[Name] [dbo].[Name] NOT NULL,
	[ShipBase] [money] NOT NULL,
	[ShipRate] [money] NOT NULL,
	[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,
	[ModifiedDate] [datetime] NOT NULL,
 CONSTRAINT [PK_ShipMethod_ShipMethodID] PRIMARY KEY CLUSTERED 
(
	[ShipMethodID] 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].[ShipMethod]  WITH CHECK ADD  CONSTRAINT [CK_ShipMethod_ShipBase] CHECK  (([ShipBase]>(0.00)))
ALTER TABLE [Purchasing].[ShipMethod] CHECK CONSTRAINT [CK_ShipMethod_ShipBase]
ALTER TABLE [Purchasing].[ShipMethod]  WITH CHECK ADD  CONSTRAINT [CK_ShipMethod_ShipRate] CHECK  (([ShipRate]>(0.00)))
ALTER TABLE [Purchasing].[ShipMethod] CHECK CONSTRAINT [CK_ShipMethod_ShipRate]
ALTER TABLE [Purchasing].[ShipMethod] ADD  CONSTRAINT [DF_ShipMethod_ShipBase]  DEFAULT ((0.00)) FOR [ShipBase]
ALTER TABLE [Purchasing].[ShipMethod] ADD  CONSTRAINT [DF_ShipMethod_ShipRate]  DEFAULT ((0.00)) FOR [ShipRate]
ALTER TABLE [Purchasing].[ShipMethod] ADD  CONSTRAINT [DF_ShipMethod_rowguid]  DEFAULT (newid()) FOR [rowguid]
ALTER TABLE [Purchasing].[ShipMethod] ADD  CONSTRAINT [DF_ShipMethod_ModifiedDate]  DEFAULT (getdate()) FOR [ModifiedDate]

Related Objects