ForeignKey Constraint from a Lookup Table

Hi,

I'm trying to figure out how to setup a Constraint (Foreign Key ) from a lookup table.

For the following example, I want to constrain to constrain so that a Car.ParkingSpotNumber is unique for a given ParkingFacility.

Any thoughts

Thanks!

Andy

Here's the scenario:

CAR table:

Id (PrimaryKey)

LicensePlateNumber

ParkingSpotNumber

PARKINGFACILITY__CAR__LOOKUP table:

Id (PrimaryKey)

CarId (Foreign Key to Car.Id)

ParkingFacilityId (Foreign Key to ParkingFacility)

PARKINGFACILITY table:

Id (PrimaryKey)

PARKINGLOT table:

Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)

Address

PARKINGGARAGE table:

Id (Foreign Key to ParkingFacility.Id) (PrimaryKey)

Address

Here's the T-SQL code to make a SQLServer2005 database for this scenario:

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Car]') AND type in (N'U'))

CREATE TABLE [dbo].[Car](

[Id] [bigint] NOT NULL,

[CustomerName] [nvarchar](50) NOT NULL,

[ParkingSpotNumber] [int] NOT NULL,

CONSTRAINT [PK_Customer] PRIMARY KEY CLUSTERED

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility]') AND type in (N'U'))

CREATE TABLE [dbo].[ParkingFacility](

[Id] [bigint] NOT NULL,

CONSTRAINT [PK_ParkingFacility] PRIMARY KEY CLUSTERED

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]') AND type in (N'U'))

CREATE TABLE [dbo].[ParkingFacility__To__Car_Lookup](

[Id] [bigint] NOT NULL,

[CarId] [bigint] NOT NULL,

[ParkingFacilityId] [bigint] NOT NULL,

CONSTRAINT [PK_ParkingFacility__To__Car_Lookup] PRIMARY KEY CLUSTERED

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingLot]') AND type in (N'U'))

CREATE TABLE [dbo].[ParkingLot](

[Id] [bigint] NOT NULL,

[Address] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_ParkingLot] PRIMARY KEY CLUSTERED

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_NULLS ON

SET QUOTED_IDENTIFIER ON

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[ParkingGarage]') AND type in (N'U'))

CREATE TABLE [dbo].[ParkingGarage](

[Id] [bigint] NOT NULL,

[Address] [nvarchar](50) NOT NULL,

CONSTRAINT [PK_ParkingGarage] PRIMARY KEY CLUSTERED

[Id] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY]

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_Car]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))

ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car] FOREIGN KEY([CarId])

REFERENCES [dbo].[Car] ([Id])

ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_Car]

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingFacility__To__Car_Lookup_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingFacility__To__Car_Lookup]'))

ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] WITH CHECK ADD CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility] FOREIGN KEY([ParkingFacilityId])

REFERENCES [dbo].[ParkingFacility] ([Id])

ALTER TABLE [dbo].[ParkingFacility__To__Car_Lookup] CHECK CONSTRAINT [FK_ParkingFacility__To__Car_Lookup_ParkingFacility]

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingLot_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingLot]'))

ALTER TABLE [dbo].[ParkingLot] WITH CHECK ADD CONSTRAINT [FK_ParkingLot_ParkingFacility] FOREIGN KEY([Id])

REFERENCES [dbo].[ParkingFacility] ([Id])

ALTER TABLE [dbo].[ParkingLot] CHECK CONSTRAINT [FK_ParkingLot_ParkingFacility]

IF NOT EXISTS (SELECT * FROM sys.foreign_keys WHERE object_id = OBJECT_ID(N'[dbo].[FK_ParkingGarage_ParkingFacility]') AND parent_object_id = OBJECT_ID(N'[dbo].[ParkingGarage]'))

ALTER TABLE [dbo].[ParkingGarage] WITH CHECK ADD CONSTRAINT [FK_ParkingGarage_ParkingFacility] FOREIGN KEY([Id])

REFERENCES [dbo].[ParkingFacility] ([Id])

ALTER TABLE [dbo].[ParkingGarage] CHECK CONSTRAINT [FK_ParkingGarage_ParkingFacility]

Hi Arnie,

Thanks for the quick reply!

Yes, from what I stated was the problem, I like your re-think on things better.

However, another twist (which I forgot to mention):

In a few facilities (with valet parking) a given car may be parked, but not in a parking spot (amazing how those valet parking guys can create spots from nothing).

So if a car is in a "real" parking spot (that has a number), then the facility/parkingspot has to be unique. However, if a car is in a "made up" parking spot (i.e. the parking spot number is null in the database), then such a "uniqueness" constraint is not-applicable.

Thanks for you help!

Andy