Tables [dbo].[Rates]
Properties
PropertyValue
CollationSQL_Latin1_General_CP1_CI_AS
Row Count (~)122
Created4:57:09 PM Thursday, September 7, 2006
Last Modified9:10:38 AM Thursday, May 23, 2024
Columns
NameData TypeMax Length (Bytes)Allow NullsIdentityDefault
Cluster Primary Key PK_Rates: RPriKeyRPriKeyint4
No
1 - 1
Foreign Keys FK_Rates_ItemCode: [dbo].[ItemCode].ICPriKeyIndexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeICPriKeyint4
No
Ratefloat8
Yes
OTRatefloat8
Yes
Foreign Keys FK_Rates_RateTypes: [dbo].[RateTypes].RTypePriKeyIndexes IX_Rates_RateType: RTypePriKeyRTypePriKeyint4
No
Foreign Keys FK_Rates_RatePlans: [dbo].[RatePlans].RPPriKeyIndexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeRPPriKeyint4
No
Foreign Keys FK_Rates_RateMatrix: [dbo].[RateMatrix].RateMatrixFIDIndexes IX_Rates_RateMatrix: RateMatrixFIDRateMatrixFIDint4
Yes
Indexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeEffectiveStartdatetime8
Yes
Indexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeEffectiveEnddatetime8
Yes
DoubleRatefloat8
Yes
Indexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeSectionvarchar(4)4
Yes
Foreign Keys FK_Rates_OriginState: [dbo].[State].OriginStateFIDIndexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeOriginStateFIDint4
Yes
Foreign Keys FK_Rates_DestinationState: [dbo].[State].DestinationStateFIDIndexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeDestinationStateFIDint4
Yes
Foreign Keys FK_Rates_OriginAirport: [dbo].[InternationalPort].OriginAirportFIDIndexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeOriginAirportFIDint4
Yes
Foreign Keys FK_Rates_DestinationAirport: [dbo].[InternationalPort].DestinationAirportFIDIndexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeDestinationAirportFIDint4
Yes
Foreign Keys FK_Rates_OriginCountryCode: [dbo].[CountryCodeStandard].OriginCountryCodeFIDIndexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeOriginCountryCodeFIDint4
Yes
Foreign Keys FK_Rates_DestinationCountryCode: [dbo].[CountryCodeStandard].DestinationCountryCodeFIDIndexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeDestinationCountryCodeFIDint4
Yes
Indexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeOriginPostalCode[dbo].[AddressPostalCode]10
Yes
Indexes IX_Rates_RatePlanItemCode: RPPriKey\ICPriKey\EffectiveStart\EffectiveEnd\Section\OriginStateFID\DestinationStateFID\OriginAirportFID\DestinationAirportFID\OriginCountryCodeFID\DestinationCountryCodeFID\OriginPostalCode\DestinationPostalCodeDestinationPostalCode[dbo].[AddressPostalCode]10
Yes
MinChargefloat8
Yes
MaxChargefloat8
Yes
Notevarchar(128)128
Yes
ForeignIDvarchar(64)64
Yes
CreatedOndatetime8
No
(getutcdate())
Indexes Indexes
NameColumnsUniqueFill Factor
Cluster Primary Key PK_Rates: RPriKeyPK_RatesRPriKey
Yes
80
IX_Rates_RatePlanItemCodeRPPriKey, ICPriKey, EffectiveStart, EffectiveEnd, Section, OriginStateFID, DestinationStateFID, OriginAirportFID, DestinationAirportFID, OriginCountryCodeFID, DestinationCountryCodeFID, OriginPostalCode, DestinationPostalCode
Yes
IX_Rates_RateMatrixRateMatrixFID80
IX_Rates_RateTypeRTypePriKey80
Triggers Triggers
NameANSI Nulls OnQuoted Identifier OnOn
RatesDeleteTrigger
Yes
No
Instead Of Delete
Check Constraints Check Constraints
NameConstraint
CK_Rates_EffectiveStartEnd(NOT ([EffectiveStart] IS NOT NULL AND [EffectiveEnd] IS NOT NULL AND [EffectiveStart]>[EffectiveEnd]))
CK_Rates_ValidDestinationPoint([DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NOT NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NOT NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NOT NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NOT NULL)
CK_Rates_ValidEffectiveDates((1)=[dbo].[RateValidEffectiveDates]([ICPriKey],[RPPriKey],[EffectiveStart],[EffectiveEnd],[Section],[OriginStateFID],[DestinationStateFID],[OriginAirportFID],[DestinationAirportFID],[OriginCountryCodeFID],[DestinationCountryCodeFID],[OriginPostalCode],[DestinationPostalCode]))
CK_Rates_ValidOriginPoint([OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NOT NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NOT NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NOT NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NOT NULL)
Foreign Keys Foreign Keys
NameColumns
FK_Rates_DestinationAirportDestinationAirportFID->[dbo].[InternationalPort].[InternationalPortID]
FK_Rates_DestinationCountryCodeDestinationCountryCodeFID->[dbo].[CountryCodeStandard].[CountryCodeStandardID]
FK_Rates_DestinationStateDestinationStateFID->[dbo].[State].[StateID]
FK_Rates_ItemCodeICPriKey->[dbo].[ItemCode].[ICPriKey]
FK_Rates_OriginAirportOriginAirportFID->[dbo].[InternationalPort].[InternationalPortID]
FK_Rates_OriginCountryCodeOriginCountryCodeFID->[dbo].[CountryCodeStandard].[CountryCodeStandardID]
FK_Rates_OriginStateOriginStateFID->[dbo].[State].[StateID]
FK_Rates_RateMatrixRateMatrixFID->[dbo].[RateMatrix].[RateMatrixID]
FK_Rates_RatePlansRPPriKey->[dbo].[RatePlans].[RPPriKey]
FK_Rates_RateTypesRTypePriKey->[dbo].[RateTypes].[RTypePriKey]
Permissions
TypeActionOwning Principal
GrantDeleteMssExec
GrantInsertMssExec
GrantSelectMssExec
GrantUpdateMssExec
SQL Script
CREATE TABLE [dbo].[Rates]
(
[RPriKey] [int] NOT NULL IDENTITY(1, 1),
[ICPriKey] [int] NOT NULL,
[Rate] [float] NULL,
[OTRate] [float] NULL,
[RTypePriKey] [int] NOT NULL,
[RPPriKey] [int] NOT NULL,
[RateMatrixFID] [int] NULL,
[EffectiveStart] [datetime] NULL,
[EffectiveEnd] [datetime] NULL,
[DoubleRate] [float] NULL,
[Section] [varchar] (4) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[OriginStateFID] [int] NULL,
[DestinationStateFID] [int] NULL,
[OriginAirportFID] [int] NULL,
[DestinationAirportFID] [int] NULL,
[OriginCountryCodeFID] [int] NULL,
[DestinationCountryCodeFID] [int] NULL,
[OriginPostalCode] [dbo].[AddressPostalCode] NULL,
[DestinationPostalCode] [dbo].[AddressPostalCode] NULL,
[MinCharge] [float] NULL,
[MaxCharge] [float] NULL,
[Note] [varchar] (128) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[ForeignID] [varchar] (64) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[CreatedOn] [datetime] NOT NULL CONSTRAINT [DF_Rates_CreatedOn] DEFAULT (getutcdate())
) ON [PRIMARY]
GO
SET QUOTED_IDENTIFIER OFF
GO
/**
*
*    This "instead of delete" trigger handles deletes for the Rates table.
*    Deletes are only performed if the RatePlan and ItemCode are not in use.
*/


CREATE trigger [dbo].[RatesDeleteTrigger] on [dbo].[Rates] instead of delete
as
    declare @theCanDeleteFlag bit
    -- If one row fails, then we abort the delete operation.
    set @theCanDeleteFlag = isnull( ( select top 1 0 from deleted where dbo.CanRateBeDeleted( deleted.ICPriKey, deleted.RPPriKey, deleted.CreatedOn ) = 0 ), 1 )

    if( @theCanDeleteFlag = 1 )
    begin
        delete from Rates
        from Rates
        inner join deleted on Rates.RPriKey = deleted.RPriKey
    end
    else
    begin
        raiserror( 'You cannot delete this Rate because the RatePlan and ItemCode are in use.', 16, 1 )
    end
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [CK_Rates_EffectiveStartEnd] CHECK ((NOT ([EffectiveStart] IS NOT NULL AND [EffectiveEnd] IS NOT NULL AND [EffectiveStart]>[EffectiveEnd])))
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [CK_Rates_ValidDestinationPoint] CHECK (([DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NOT NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NOT NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NOT NULL AND [DestinationPostalCode] IS NULL OR [DestinationStateFID] IS NULL AND [DestinationAirportFID] IS NULL AND [DestinationCountryCodeFID] IS NULL AND [DestinationPostalCode] IS NOT NULL))
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [CK_Rates_ValidEffectiveDates] CHECK (((1)=[dbo].[RateValidEffectiveDates]([ICPriKey],[RPPriKey],[EffectiveStart],[EffectiveEnd],[Section],[OriginStateFID],[DestinationStateFID],[OriginAirportFID],[DestinationAirportFID],[OriginCountryCodeFID],[DestinationCountryCodeFID],[OriginPostalCode],[DestinationPostalCode])))
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [CK_Rates_ValidOriginPoint] CHECK (([OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NOT NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NOT NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NOT NULL AND [OriginPostalCode] IS NULL OR [OriginStateFID] IS NULL AND [OriginAirportFID] IS NULL AND [OriginCountryCodeFID] IS NULL AND [OriginPostalCode] IS NOT NULL))
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [PK_Rates] PRIMARY KEY CLUSTERED  ([RPriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [IX_Rates_RatePlanItemCode] UNIQUE NONCLUSTERED  ([RPPriKey], [ICPriKey], [EffectiveStart], [EffectiveEnd], [Section], [OriginStateFID], [DestinationStateFID], [OriginAirportFID], [DestinationAirportFID], [OriginCountryCodeFID], [DestinationCountryCodeFID], [OriginPostalCode], [DestinationPostalCode]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Rates_RateMatrix] ON [dbo].[Rates] ([RateMatrixFID]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [IX_Rates_RateType] ON [dbo].[Rates] ([RTypePriKey]) WITH (FILLFACTOR=80) ON [PRIMARY]
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_DestinationAirport] FOREIGN KEY ([DestinationAirportFID]) REFERENCES [dbo].[InternationalPort] ([InternationalPortID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_DestinationCountryCode] FOREIGN KEY ([DestinationCountryCodeFID]) REFERENCES [dbo].[CountryCodeStandard] ([CountryCodeStandardID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_DestinationState] FOREIGN KEY ([DestinationStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_ItemCode] FOREIGN KEY ([ICPriKey]) REFERENCES [dbo].[ItemCode] ([ICPriKey])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_OriginAirport] FOREIGN KEY ([OriginAirportFID]) REFERENCES [dbo].[InternationalPort] ([InternationalPortID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_OriginCountryCode] FOREIGN KEY ([OriginCountryCodeFID]) REFERENCES [dbo].[CountryCodeStandard] ([CountryCodeStandardID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_OriginState] FOREIGN KEY ([OriginStateFID]) REFERENCES [dbo].[State] ([StateID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_RateMatrix] FOREIGN KEY ([RateMatrixFID]) REFERENCES [dbo].[RateMatrix] ([RateMatrixID])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_RatePlans] FOREIGN KEY ([RPPriKey]) REFERENCES [dbo].[RatePlans] ([RPPriKey])
GO
ALTER TABLE [dbo].[Rates] ADD CONSTRAINT [FK_Rates_RateTypes] FOREIGN KEY ([RTypePriKey]) REFERENCES [dbo].[RateTypes] ([RTypePriKey])
GO
GRANT SELECT ON  [dbo].[Rates] TO [MssExec]
GRANT INSERT ON  [dbo].[Rates] TO [MssExec]
GRANT DELETE ON  [dbo].[Rates] TO [MssExec]
GRANT UPDATE ON  [dbo].[Rates] TO [MssExec]
GO
Uses