Hej alle
Jeg bruger MSSQL2008. og jeg er kørt helt fast og håber på én der kender til
grundlæggende DB design.
Jeg har 2 tabeller. Den ene er en simpel lookup tabel den anden er en tabel
der bruger lookup tabellen. Vi kunne f.eks kalde tabel 1 Contacts og den
anden Table1. Jeg vil bruge referentiel integritet så 'DB-motoren' opdater
relateret felter korrekt.
Et Eksempel:
Jeg har en lookup tabel der Contacts samt Tabel1 som der gør brug af Contacs
tabelen.
Contacts består kun af ét felt f.eks (nvarchar 50) som også er sat som
primær nøgle.
Her er CREATE TO scriptet jeg fik via Microsoft SQL Server Manageren.
USE [TEST]
GO
/****** Object: Table [dbo].[Contacts] Script Date: 07/01/2010 14:43:47
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Contacts](
[Contact_PK] [nvarchar](50) NOT NULL,
CONSTRAINT [PK_Supplier] PRIMARY KEY CLUSTERED
(
[Contact_PK] 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
Jeg vil gerne have en anden tabel der slår op i lookup tabellen. Ikke bare
med ét felt men med flere felter
a'la.:
Primærnøgle
Felt1
Felt2
ContactPersonID
ExpertID
Og de to ID-felter er jo fremmed nøgle felter som jeg gerne vil have
relateret til primær tabelen. Så via Manageren går jeg ind og opretter to
relationer med hver sit navn (FK_Table1_ContactPerson samt FK_Table1_Expert)
Og CREATE TO scriptet for denne tabel er følgende.:
USE [TEST]
GO
/****** Object: Table [dbo].[Table1] Script Date: 07/01/2010 15:02:16
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[Tablel1_PK] [int] NOT NULL,
[Field1] [real] NULL,
[Field2] [real] NULL,
[ContactPerson_ID] [nvarchar](50) NULL,
[Expert_ID] [nvarchar](50) NULL,
CONSTRAINT [PK_Spareparts] PRIMARY KEY CLUSTERED
(
[Tablel1_PK] 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].[Table1] WITH CHECK ADD CONSTRAINT
[FK_Table1_ContactPerson] FOREIGN KEY
([ContactPerson_ID])
REFERENCES [dbo].[Contacts] ([Contact_PK])
GO
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_ContactPerson]
GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Expert]
FOREIGN KEY([Expert_ID])
REFERENCES [dbo].[Contacts] ([Contact_PK])
GO
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Expert]
GO
Så langt - så godt.
Nu vil jeg også gerne lave noget referentiel integritet så DB-motoren sørger
for, at når der rettes i en record - bliver alle records korrekt opdateret.
Og når der slettes en record bliver ID felterne i Tabel1 sat til NULL
Igen via Manageren går jeg ind på den første af mine 2 relationer og klikker
på den første relation og går ind på "INSERT and UPDATE Specification".
"Delete rule" sætter jeg til "Set Null" og "Update rule" sættes til
"Cascade".
Scriptet ser nu sådan her ud.:
USE [TEST]
GO
/****** Object: Table [dbo].[Table1] Script Date: 07/01/2010 16:05:00
******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Table1](
[Tablel1_PK] [int] NOT NULL,
[Field1] [real] NULL,
[Field2] [real] NULL,
[ContactPerson_ID] [nvarchar](50) NULL,
[Expert_ID] [nvarchar](50) NULL,
CONSTRAINT [PK_Spareparts] PRIMARY KEY CLUSTERED
(
[Tablel1_PK] 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].[Table1] WITH CHECK ADD CONSTRAINT
[FK_Table1_ContactPerson] FOREIGN KEY
([ContactPerson_ID])
REFERENCES [dbo].[Contacts] ([Contact_PK])
ON UPDATE CASCADE
ON DELETE SET NULL
GO
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_ContactPerson]
GO
ALTER TABLE [dbo].[Table1] WITH CHECK ADD CONSTRAINT [FK_Table1_Expert]
FOREIGN KEY([Expert_ID])
REFERENCES [dbo].[Contacts] ([Contact_PK])
GO
ALTER TABLE [dbo].[Table1] CHECK CONSTRAINT [FK_Table1_Expert]
GO
Jeg gemmer nu tabelen og går ind og sætte de tilsvarende properties på den
anden relation (FK_Table1_Expert).
Men nu fejler den med nedenstående error - øv!!
'Contacts' table saved successfully
'Table1' table
- Unable to create relationship 'FK_Table1_Expert'.
Introducing FOREIGN KEY constraint 'FK_Table1_Expert' on table 'Table1' may
cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON
UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Could not create constraint. See previous errors.
Jeg kan ikke helt "tyde" den error.
Det lykkedes på den ene relation med ikke den anden - det forstår jeg
overhovedet ikke.
Jeg har enda tjekket om i det mindste den ene (ContactPerson_ID bliver
opdateret som den skal - og det virker fint.
Det er sikkert nok mig der ikke helt har forstået emnet om referentiel
integritet helt - eller om constraints for den sags skyld.
Er min problemstilling ikke klar nok, prøv evt at execute ovennævte script.
Er der en løsning og er der mon en derude med en nærmere forklaring?
På forhånd tak.
Carsten
|