[SQL 2000] Composite primary / foreign keys

[BikkelZ]

Ik heb twee tabellen, waarvan er een bestaat uit een koppeltabel met drie foreign keys (die ook weer refereren naar andere tabellen, niet getoond), die tesamen een primary key vormen:

SQL:

CREATE TABLE [dbo].[ProjectEmployeeRole](
    [PERProjectId] [int] NOT NULL,
    [PEREmployeeId] [int] NOT NULL,
    [PERProjectRoleId] [int] NOT NULL,
 CONSTRAINT [PK_ProjectEmployeeRole] PRIMARY KEY CLUSTERED 
(
    [PERProjectId] ASC,
    [PEREmployeeId] ASC,
    [PERProjectRoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Naar die tabel wordt gerefereerd naar de onderstaande tabel door de drie matchende velden:

SQL:

CREATE TABLE [dbo].[ProjectEmployeeHourrateHistory](
    [PEHEmployeeId] [int] NOT NULL,
    [PEHProjectId] [int] NOT NULL,
    [PEHHourrate] [money] NOT NULL,
    [PEHStart] [datetime] NOT NULL,
    [PEHProjectRoleId] [int] NULL,
    [PEHTravelExpensesPaidPerKilometer] [money] NULL,
    [PEHAreTravelHoursPaid] [bit] NOT NULL,
 CONSTRAINT [PK_ConsultantProjectHourrateHistory] PRIMARY KEY CLUSTERED 
(
    [PEHEmployeeId] ASC,
    [PEHProjectId] ASC,
    [PEHStart] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Maar iedere keer als ik die constraint probeer te leggen, krijg ik een error "The columns in table 'ProjectEmployeeRole' do not match an existing primary key or UNIQUE constraint.'.

Naar mijn idee zijn de velden in ProjectEmployeeRole gewoon afgedwongen uniek, dus wat gaat er mis?

[whoami]

Hoe probeer je die FK te leggen ? Toon eens de SQL code ...
Heeft het misschien te maken met het feit dat PEHProjectRoleId nullable is ? In de andere tabel is dit veld nl. niet nullable; als dat veld deel uitmaakt van een FK, dan kan PEHProjectRoleId toch nooit een NULL bevatten, aangezien je nooit een PK zult hebben in de andere tabel waarin dat veld NULL is.

Trouwens: als je een surrogate primary key zou gebruiken, dan heb je dit probleem niet.

[EfBe]

De FK kan alleen van ProjectEmployeeHourrateHistory naar ProjectEmployeeRole worden gelegd, niet andersom. FK is altijd van FK side to PK side.

[BikkelZ]

whoami schreef op donderdag 13 november 2008 @ 09:32:
Hoe probeer je die FK te leggen ? Toon eens de SQL code ...
Heeft het misschien te maken met het feit dat PEHProjectRoleId nullable is ? In de andere tabel is dit veld nl. niet nullable; als dat veld deel uitmaakt van een FK, dan kan PEHProjectRoleId toch nooit een NULL bevatten, aangezien je nooit een PK zult hebben in de andere tabel waarin dat veld NULL is.

Trouwens: als je een surrogate primary key zou gebruiken, dan heb je dit probleem niet.

Die NULL heb ik geprobeerd, ik had goede hoop maar dat was het toch ook niet.

Een compositie key houdt in dat de employee en het project en de rol afgedwongen het zelfde is als in die koppeltabel. Als ik een surrogaatkey gebruik dan kan aan de achterkant in die koppeltabel het projectnummer, de employee of de rol veranderen zonder dat dat gevolgen heeft voor die history tabel.

Ik heb het in dit geval uiteindelijk toch maar naar een surrogaatkeykoppeling veranderd, maar het blijft wel vreemd dat een afgedwongen unieke primary key niet als foreign key kan dienen, dat zou namelijk volgens de regels van normalisatie gewoon moeten kunnen.

Wordt een surrogaatkey niet pas standaard vanaf Boyce Codd of de vierde normaalvorm?

[BikkelZ]

Na nog wat schuiven met tabellen en aanpassen ziet de tabel er nu als volgt uit:

SQL:

CREATE TABLE [dbo].[ProjectEmployee](
    [PREEmployeeId] [int] NOT NULL,
    [PREProjectId] [int] NOT NULL,
    [PREProjectRoleId] [int] NOT NULL,
    [PREIsActive] [bit] NOT NULL,
    [PREHoursMustBeSigned] [bit] NOT NULL,
    [PREHoursAssigned] [int] NOT NULL,
 CONSTRAINT [PK_ProjectEmployee_1] PRIMARY KEY CLUSTERED 
(
    [PREEmployeeId] ASC,
    [PREProjectId] ASC,
    [PREProjectRoleId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY],
 CONSTRAINT [IX_ProjectEmployee] UNIQUE NONCLUSTERED 
(
    [PREProjectRoleId] ASC,
    [PREEmployeeId] ASC,
    [PREProjectId] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

Kortom, je kunt dus geen composite primary key refereren zonder dat je daar ook nog een unique overheen gooit!

Het grappige is dat je bij bijvoorbeeld MySQL juist geen unique index moet leggen omdat de PK al een unique index van zichzelf is.

[EfBe]

BikkelZ schreef op donderdag 13 november 2008 @ 11:38:
Kortom, je kunt dus geen composite primary key refereren zonder dat je daar ook nog een unique overheen gooit!

Onzin:

code:

CREATE TABLE [dbo].[PkSide](
    [ID1] [int] NOT NULL,
    [ID2] [int] NOT NULL,
    [Name] [varchar](50) NOT NULL,
 CONSTRAINT [PK_PkSide] PRIMARY KEY CLUSTERED 
(
    [ID1] ASC,
    [ID2] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[FkSide](
    [ID] [int] NOT NULL,
    [FkID1] [int] NOT NULL,
    [FkID2] [int] NOT NULL,
 CONSTRAINT [PK_FkSide] 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]

GO
ALTER TABLE [dbo].[FkSide]  WITH CHECK ADD  CONSTRAINT [FK_FkSide_PkSide] FOREIGN KEY([FkID1], [FkID2])
REFERENCES [dbo].[PkSide] ([ID1], [ID2])
GO
ALTER TABLE [dbo].[FkSide] CHECK CONSTRAINT [FK_FkSide_PkSide]

[ Voor 10% gewijzigd door EfBe op 13-11-2008 12:43 . Reden: vergat fk ]