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

GO

SET QUOTED_IDENTIFIER ON

GO

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

BEGIN

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]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

BEGIN

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]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

BEGIN

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]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

BEGIN

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]

END

GO

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

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

BEGIN

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]

END

GO

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])

GO

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

GO

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])

GO

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

GO

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])

GO

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

GO

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])

GO

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