[SQL] XOR foreign key

[BramBo]

Bij het designen van een database ben ik tegen een probleem aangelopen.

Het probleem komt er op neer dat ik 1 van 2 foreign key colums wil setten. de andere moet NULL blijven.

Het is de bedoeling dat 'CalLSettings_Monteur_Group' OF een monteurID OF een GroupID bevat. Afhankelijk wat voor type oproep (groep of monteur).

Ik heb de volgende table definitie:

SQL:

Create table [dbo].[Call_Settings]
        [ID] Integer Identity(1,1) NOT NULL,
        -- meer call eigenschappen
Primary Key ([ID]) ON [PRIMARY]

Create table [dbo].[Group]
    [ID] Integer Identity(1,1) NOT NULL,
    -- meer group eigenschappen
Primary Key ([ID]) ON [PRIMARY]

Create table [dbo].[Monteur]
    [ID] Integer Identity(1,1) NOT NULL,
    -- meer monteur eigenschappen
Primary Key ([ID]) ON [PRIMARY]

Create table [dbo].[CalLSettings_Monteur_Group]
    [ID] Integer Identity(1,1) NOT NULL,
    [CSID] Integer NOT NULL,
    [MonteurID] Integer,
    [GroupID] Integer,
Primary Key ([ID],[CSID]) ON [PRIMARY]

de foreign key's waar het om gaat zijn :

SQL:

ALTER TABLE [dbo].[CalLSettings_Monteur_Group]  WITH CHECK ADD FOREIGN KEY([GroupID])
REFERENCES [dbo].[Group] ([ID])

ALTER TABLE [dbo].[CalLSettings_Monteur_Group]  WITH CHECK ADD FOREIGN KEY([MonteurID])
REFERENCES [dbo].[Monteur] ([ID])

ALTER TABLE [dbo].[CalLSettings_Monteur_Group]  WITH CHECK ADD FOREIGN KEY([CSID])
REFERENCES [dbo].[Call_Settings] ([ID])
ON UPDATE CASCADE
ON DELETE CASCADE

Ik heb het geprobeerd om via een constrain af te dwingen, omdat MS-SQL server 2005 (de uiteindelijke server) de XOR niet ondersteund. heb ik een AND/OR check gemaakt:

SQL:

ADD CONSTRAINT [Check_Call_Type] 
CHECK (([MonteurID]=NULL AND [GroupId]<>NULL) OR ([GroupID]=NULL AND [MonteurID]<>NULL))

Ik zou verwachten dat hij op deze manier blokkeert dat ik in zowel een GroupID als een MonteurID kan invoeren. Maar wel afdwingt DAT er 1 ingevuld is. En dat degene welke is ingevuld, goed wordt opgepakt door de foreign key, waardoor de intergriteit gewaarborgt blijft.

Echter kan ik op deze manier nog altijd zowel 2 maal 'NULL' als 2 geldige waarden invullen. Deze geldigheid wordt wel gecheckt. de foreign key constrains werken dus wel naar behoren.

Omdat het MS-SQL server 2005 betreft heb ik ook al op MSDN rondgekeken. hier heb ik echter niets kunnen vinden over dit specifieke probleem (foreign key "keuze").

[kenneth]

SQL:

Foo <> NULL

is sowieso fout.

SQL:

Foo IS NOT NULL

edit, en zo moet ook:

SQL:

Foo = NULL

veranderd worden in

SQL:

Foo IS NULL

[ Voor 44% gewijzigd door kenneth op 02-03-2006 14:49 ]

[curry684]

Dat is meteen ook de grofste fout die het wel zal oplossen. NULL = NULL is altijd false, evenals NULL <> NULL. Optional values volgen een hele eigen waarheidstabel

Illustratie uit SQL Server Books Online:

Comparing Null Values
Care must be taken when comparing null values. The behavior of the comparison depends on the setting of the SET ANSI_NULLS option.

When SET ANSI_NULLS is ON, a comparison in which one or more of the expressions is NULL does not yield either TRUE or FALSE; it yields UNKNOWN. This is because a value that is unknown cannot be compared logically against any other value. This occurs if either an expression is compared to the literal NULL, or if two expressions are compared and one of them evaluates to NULL. For example, this comparison always yields UNKNOWN when ANSI_NULLS is ON:

ytd_sales > NULL

This comparison also yields UNKNOWN any time the variable contains the value NULL:

ytd_sales > @MyVariable

Use the IS NULL or IS NOT NULL clauses to test for a NULL value. This can add complexity to the WHERE clause. For example, the Region column in the Northwind Customers table allows null values. If a SELECT statement is to test for null values in addition to others, it must include an IS NULL clause:

SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region IN ('WA', 'SP', 'BC')
OR Region IS NULL

Transact-SQL supports an extension that allows for the comparison operators to return TRUE or FALSE when comparing against null values. This option is activated by setting ANSI_NULLS OFF. When ANSI_NULLS is OFF, comparisons such as ColumnA = NULL return TRUE when ColumnA contains a null value and FALSE when ColumnA contains some value besides NULL. Also, a comparison of two expressions that have both evaluated to null values yields TRUE. With ANSI_NULLS set OFF, this SELECT statement returns all the rows in the Customer table for which Region is a null value:

SELECT CustomerID, CompanyName, Region
FROM Northwind.dbo.Customers
WHERE Region = NULL

Regardless of the ANSI_NULLS setting, Null values are always considered equal for the purposes of the ORDER BY, GROUP BY, and DISTINCT keywords. Also, a unique index or UNIQUE constraint that allows NULL can contain only one row with a NULL key value. A subsequent row with NULL is rejected. A primary key cannot have NULL in any column that is part of the key.

Computations involving NULL evaluate to NULL because the result must be UNKNOWN if any of the factors is unknown. For example, column1 + 1 evaluates to NULL if column1 is NULL.

When the columns being searched include those defined as allowing null values, you can find null or nonnull values in the database with this pattern:

WHERE column_name IS [NOT] NULL

[ Voor 89% gewijzigd door curry684 op 02-03-2006 14:54 ]

[ATS]

XOR kan je omschrijven als:

(A OR B ) AND NOT (A AND B )

toch? Anyway, een simpel waarheidstabelletje kan je zo vertellen of dat klopt, en zo niet, helpen met het oplossen van de omschrijving.

[ Voor 1% gewijzigd door ATS op 02-03-2006 14:54 . Reden: smilies zaten in de weg ]

[BramBo]

tnx, geprobeerd en het werkt perfect!

Stom dat ik daar zelf niet eerder aan gedacht heb. Was het al weer veel moeilijker aan het zoeken. (maarja, dat zal wel een vaker voorkomend probleem zijn )

in ieder geval heel erg bedankt!

[JKVA]

ATS schreef op donderdag 02 maart 2006 @ 14:53:
XOR kan je omschrijven als:

(A OR B ) AND NOT (A AND B )

XOR is in feite een check of twee booleans ongelijk zijn. In Java bijvoorbeeld dit

Java:

boolean a = true;
boolean b = false;
if (a != b)

In jouw SQL geval kun je dat ook zo schrijven

SQL:

(A IS NULL) <> (B IS NULL)

Dit kan omdat de IS NULL statements in een boolean resulteren en op die booleans kun je de <> weer loslaten om een XOR te krijgen.