Need a one to one relationship

Can anyone tell me how I can create a one to one relationship from the
following ddl? I feel like I'm close to having this working, but can't
quite get it. I think I'm not sure what I should do with the
PurchaseOrderItem.BuildID. The business rule that I am trying to infoce
is one DistributorNumber per Build as well as one DistributorNumber per
PurchaseOrder while maintaining a one to one relationship betweetn the
row in the BuildItem and PurchaseOrderItem table.
CREATE TABLE [BuildItem] (
[BuildID] int NOT NULL,
[DistributorNumber] varchar(30) DEFAULT ('') NOT NULL,
[DistributorID] int NOT NULL,
[Quantity] tinyint DEFAULT (0) NOT NULL,
[UnitGrams] decimal(7,2) DEFAULT (0) NOT NULL,
[UnitCostPrice] smallmoney DEFAULT (0) NOT NULL,
[UnitSalePrice] smallmoney DEFAULT (0) NOT NULL
)
GO
ALTER TABLE [BuildItem] ADD CONSTRAINT [PK_BuildItem]
PRIMARY KEY CLUSTERED ([BuildID], [DistributorNumber])
GO
ALTER TABLE [BuildItem] ADD CONSTRAINT [FK_BuildItem_Build]
FOREIGN KEY ([BuildID]) REFERENCES [Build] ([BuildID])
GO
CREATE TABLE [PurchaseOrderItem] (
[PurchaseOrderID] int NOT NULL,
[DistributorNumber] varchar(30) NOT NULL,
[BuildID] int,
[ItemDescription] varchar(100) DEFAULT ('') NOT NULL,
[QuantityOrdered] tinyint DEFAULT (0) NOT NULL,
[QuantityReceived] tinyint DEFAULT (0) NOT NULL,
[QuantityBackOrdered] tinyint DEFAULT (0) NOT NULL,
[UnitCost] smallmoney DEFAULT (0) NOT NULL,
)
GO
ALTER TABLE [PurchaseOrderItem] ADD CONSTRAINT [PK_PurchaseOrderItem]
PRIMARY KEY ([PurchaseOrderID], [DistributorNumber])
GO
ALTER TABLE [PurchaseOrderItem] ADD CONSTRAINT
[FK_PurchaseOrderItem_BuildItem]
FOREIGN KEY ([BuildID], [DistributorNumber]) REFERENCES [BuildItem]
([BuildID], [DistributorNumber])
GO
Regards,
Aaron1-to-1 relationships sometimes share the same PK, except that in the
"other" table it's a PK and FK
1:1 Customer to Address
Create Table Customer (
CustomerID INT IDENTITY,
CustomerName NVARCHAR(30)
PRIMARY KEY CLUSTERED CustomerID)
Create Table Address (
CustomerID INT REFERENCES Customer,
Address NVARCHAR(30)
PRIMARY KEY CustomerID )
The other way to enforce the 1:1 is to put a unique index on the "other"
table. In the last example, let's say you wanted the PK to be an
AddressID because you thought the requirements might change in the
future to allow more than one address. You could "temporarily" enforce
the 1:1 using:
Create Table Address (
AddressID INT IDENTITY,
CustomerID INT REFERENCES Customer,
Address NVARCHAR(30)
PRIMARY KEY AddressID )
Create Unique Clustered Index Address_IDX on dbo.Address(CustomerID)
David Gugick
Imceda Software
www.imceda.com