Miff
Miff

Reputation: 15

MySQL UNIQUE still allows duplicate row insert when foreign key columns are NULL

I Have 3 tables ProdVariant, Variant, VariantValues.

ProductVariant
----------------------------             
ProductVariantID  PK
ProductID         FK NOT NULL
VariantID         FK NULL
VariantValuesID   FK NULL

Variant
-----------------------------
VariantID         PK
 

VariantValues
------------------------------
VariantValuesID   PK       
VariantID         FK NOT NULL  

                      

A product can be in the ProductVariant table if it has no Variants and no VariantValues which is when both those fields will be NULL, but it cant have one or the other. The row has to be either both its Variant and VariantValues fields as NULL or both with a numeric value.

I put a UNIQUE constraint on this table

ALTER TABLE ProductVariant 
ADD CONSTRAINT ProdVarVarValUnique UNIQUE (ProductID, VariantID, VariantValuesID);

This UNIQUE constraint works for all input unless I try and enter a new row with the same values where the VariantID and VariantValuesID as NULL and the new row entered both these columns are NULL. I need to NOT allow a duplicate row entered if an existing row is

ProductID
1                

VariantID
NULL        

VariantValuesID
NULL

When I input a new row with these same values it will allow it

If a unique doesnt restrict NULL is there any way I can keep this unique constraint that works for every other situation but also add a CHECK that will not allow a new entry if the input for Variant or VariantValues is NULL and a duplicate ProductID is already in the table? Im hoping to do this without having to do a Trigger. I thought about just setting the VariantID and VariantValuesID field settings in the table to NOT NULL and then just inserting and leaving a row in the Variants and VariantValues tables that have all NULL fields. The VariantID and VariantValuesID fields BOTH have to be NULL or BOTH fields have to have an INT value.

Upvotes: 0

Views: 31

Answers (0)

Related Questions