Multi-Table Foreign Key & Referential Integrity

Question

I have a database where I'm trying to ensure referential integrity using foreign keys but I'm unsure how to proceed:

There are four tables: Company, Employee, Order and BillingCode.

A company has multiple employees, and each of those employees can place orders. Each order must be allocated a billing code - which is taken from a lookup table. Each company can have multiple billing codes.
An employee can only use a billing code thats allocated to their company.

Basic Diagram: http://i61.tinypic.com/21bm937.png

CREATE TABLE [dbo].[BillingCodes](
    [BillingId] [int] NOT NULL,
    [CompanyId] [int] NOT NULL,
    [BillingCode] [varchar](10) NOT NULL,
 CONSTRAINT [PK_BillingCodes] PRIMARY KEY CLUSTERED 
(
    [BillingId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Company](
    [CompanyId] [int] NOT NULL,
    [CompanyName] [varchar](50) NOT NULL,
 CONSTRAINT [PK_Company] PRIMARY KEY CLUSTERED 
(
    [CompanyId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Employees](
    [EmployeeId] [int] NOT NULL,
    [CompanyId] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL
 CONSTRAINT [PK_Employees] PRIMARY KEY CLUSTERED 
(
    [EmployeeId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[Orders](
    [OrderId] [int] NOT NULL,
    [EmployeeId] [int] NOT NULL,
    [Price] [money] NOT NULL,
    [Qty] [int] NOT NULL,
    [BillingId] [int] NOT NULL,
 CONSTRAINT [PK_Orders] PRIMARY KEY CLUSTERED 
(
    [OrderId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[BillingCodes]  WITH CHECK ADD  CONSTRAINT [FK_BillingCodes_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([CompanyId])
GO

ALTER TABLE [dbo].[Employees]  WITH CHECK ADD  CONSTRAINT [FK_Employees_Company] FOREIGN KEY([CompanyId])
REFERENCES [dbo].[Company] ([CompanyId])
GO

ALTER TABLE [dbo].[Orders]  WITH CHECK ADD  CONSTRAINT [FK_Orders_Employees] FOREIGN KEY([EmployeeId])
REFERENCES [dbo].[Employees] ([EmployeeId])
GO

How do I ensure that [BillingId] in [Orders] is for the same company as the Employee?

Normally I'd just create a Foreign key between [BillingCodes] and [Orders] on [BillingId] and enforce the [CompanyId] reference in the business layer. Unfortunately in this instance I have a boss who likes to go into the database and correct data manually. So I need to enforce this in the database.

Is my only option to carry the CompanyID over to the orders table, and use a composite foreign key to [BillingCode]? This is a simplified version - there are another 3 tables between [Company] and [Employee], so I'd rather not have [CompanyId] in every table if I don't need to.

Is there an easier way to accomplish this?

Answer 1

You can do this using just referential integrity constraints, it just takes some redundancy -- which is not altogether a bad thing to have.

First, you have to bring the CompanyID along with the EmployeeID to the Orders table and make it referable to the Employee table.

alter table Employees add unique constraint UQ_Employee_Co( EmployeeID, CompanyID );
alter table BillingCodes add unique constraint UQ_BillingCodes_Company_Code( BillingID, CompanyID );
-- Or use indexes - no matter.

CREATE TABLE Orders(
    OrderId    int NOT NULL,
    EmployeeId int NOT NULL,
    CompanyID  int not null,
    Price      money NOT NULL,
    Qty        int NOT NULL,
    BillingId  int NOT NULL,
    CONSTRAINT PK_Orders PRIMARY KEY( OrderID ),
    constraint FK_Orders_Employee foreign key( EmployeeID, CompanyID )
        references Employees( EmployeeID, CompanyID ),
    constraint FK_Orders_BillingCode foreign key( BillingID, CompanyID )
        references BillingCodes( BillingID, CompanyID ),
);

Now you cannot have an order with an EmployeeID that associates with one company and a BillingID that associates with a different company because you cannot have an order with EmployeeID and CompanyID that don't match what's in the Employee table nor a CompanyID and BillingID that don't match what's in the BillingCodes table.

And all it costs you is one additional column.

Answer 2

An alternative is to add a trigger for insert and update on Orders. This would validate that the BillingId is linked to the same Company that the EnployeeId is. If they end up at different companies then raise an error. This is probably the way I'd do this.