none
Conditional Unique Constraint

    질문

  • Hi everybody,

    I've checked SQL Server documentation and don't see that question answered. I didn't do more searches on that topic.

    I want to introduce a unique constraint on email but only when a particular setting in a different table is set to true. What would be the proper way to introduce such logic?

    Thanks in advance.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2018년 5월 17일 목요일 오후 3:55
    중재자

모든 응답

  • Sounds like check constraint is what you're after

    Not fully clear on your scenario though!


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 17일 목요일 오후 4:01
  • Would you please clarify?

    The condition is relatively simple. I have email column in Contacts table. It has to be unique but only when some specific setting in the Settings table (where we use SettingName SettingVal approach) is set to true. If that setting is false, then we don't want to have email column unique.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2018년 5월 17일 목요일 오후 5:00
    중재자
  • What happens when there is a duplicate email in that column and settingName is modified to have column unique?

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 5월 17일 목요일 오후 6:29
  • This is a very good question. We're supposed to validate the data before changing this setting. If there are duplicates already we provide a warning and don't allow to change that setting.

    This is, of course, meaning from the application point of view. There needs to be a check on DB site as well, I imagine. That was not even thought of. We do some of the restriction logic only on the application side. The question I'm asking right now is also supposed to be on the application side first, but I'm thinking we may want a good prevention in back-end as well.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    2018년 5월 17일 목요일 오후 6:44
    중재자
  • Hi Naomi,

    This can be achieved by applying Check constraint on Contacts table in your case. In order to apply check constraint, You need to perform below 2 steps:

    1. Create a relationship between both tables if it is not there.

    2. Scalar function has to be created and used in the Check constraint with Contacts table.

    Below are the steps you may have to apply to implement conditional check on Contacts table. Hope this will give you an idea to apply in your case:

    --*/ 1. Create dummy table dbo.Contacts and db.Settings *--/
    CREATE TABLE dbo.Contacts (
    	id INT ,
    	Email varchar(20)
    )    
    GO
    
    INSERT INTO  dbo.Contacts (id,Email)
    VALUES	
    (1,'Test@MSDN.com'),
    	(2,'Dummy@MSDN.com')
    GO
    
    CREATE TABLE dbo.Settings (
    	ID INT NULL,
    	Flag varchar(20)
    )    
    GO
    
    insert into dbo.Settings
    VALUES	(1,'False')
    
    ---*/ 2- Create Function on table dbo.Settings to return the flag value True or False---- 
    CREATE FUNCTION [dbo].[UQ_duplicates_Check](@ID INT)
    RETURNS VARCHAR(20)
    AS 
    BEGIN
    	DECLARE	@ret_value VARCHAR(20) 
     
    	select @ret_value=Flag from dbo.Settings
    	WHERE ID=@ID
     
    	RETURN @ret_value
    END
    GO
    
    
    ---*/ 3- Alter table dbo.Contacts to apply check constraint---- 
    
    ALTER TABLE dbo.Contacts WITH NOCHECK 
    	ADD CONSTRAINT UQ_duplicates_with_function 
    				CHECK (dbo.UQ_duplicates_Check(ID)='False')
    GO
    
    ---*/ 4- Testing by updating flags ---- 
    Update dbo.Settings
    set Flag='True'  --Control flag on Settings table to add unique/non-unique records in dbo.Contacts table.
    
    --Try to re-enter same records
    INSERT INTO  dbo.Contacts (id,Email)
    VALUES	
    (1,'Test@MSDN.com'),
    	(2,'Dummy@MSDN.com')
    GO
    
    --/* If flag is set up as True below check constraint error will be resulted
    --Msg 547, Level 16, State 0, Line 54
    --The INSERT statement conflicted with the CHECK constraint "UQ_duplicates_with_function". The conflict occurred in  table "dbo.Contacts", column 'id'.
    --The statement has been terminated.

    Hope this helps!

    Thanks,

    Alambir

    Please vote this as helpful or Mark this as Answered if this helps


    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!


    2018년 5월 17일 목요일 오후 6:58
  • Unfortunately, this is not the correct solution as it would prevent any insertions, not just the duplicates.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2018년 5월 17일 목요일 오후 7:15
    중재자
  • I don't want to sound negative but the thought of having dynamic unique constraint on column and any approach taken to achieve may be complicated.

    If there are more than record for email address is possible , probably move email address id and PK to a different table ? Am assuming there is another column which is primary key.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 5월 17일 목요일 오후 7:19
  • Here is one way.  Create a foreign key with ON UPDATE CASCADE from the Contacts table to the Settings table.  And add a filtered unique index on the email address.  That could look something like

    Create Table dbo.Settings(EmailMustBeUnique bit Not Null Unique);
    Create Table dbo.Contacts(Id int identity Primary Key,
       EmailAddress varchar(50) Not Null,
       EmailMustBeUnique bit Not Null, 
       Constraint FKEmail Foreign Key (EmailMustBeUnique) References dbo.Settings(EmailMustBeUnique) On Update Cascade)
    Create Unique Index ContactsEmail On dbo.Contacts(EmailAddress) Where EmailMustBeUnique = 1;

    That would a) allow duplicates when the Settings value was zero, 2) reject duplicates when the Settings value was one, and 3) automatically reject any attempt to change the Settings value to one if there currently are duplicate email addresses in the Contacts table.

    Of course, anything you do will have some disadvantages.  I think this technique has 2.  First, you will have to change the process that inserts new contacts.  That process would now have to get the current value in the Settings table and enter it into the Contacts table.  My guess is that's not a big problem.  But the second problem is that every time you change the value in the Settings table, SQL would have to update every row in the Contacts table.  That could be expensive depending on how often you change the value in Settings and how many rows you have in the Contacts table.

    Tom

    2018년 5월 17일 목요일 오후 8:06
  • Unfortunately, this is not the correct solution as it would prevent any insertions, not just the duplicates.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    In that case you could add trigger on Contact table which will trigger when try to load duplicates records with condition True.

     Hope it helps!

    Thanks, Alambir Please vote this as helpful or Mark as answered if this helps!


    2018년 5월 17일 목요일 오후 9:24
  • I actually forgot. The emails are in its own table and we have also a linking table. One Contact may have several emails (but only one email per type, I think). Also, the condition I was talking about should apply for guests only (I think), but the contacts table serve for all other entities we have (operators, instructors, vendors, clients).

    So, the above business logic would translate into something complex - unique emails but only for guests. So, theoretically, two operators may share the same email, but emails are stored in Emails table and there is ContactEmailAddresses table linking Contacts with Emails and each guest record, each operator record, etc. has a ContactId linking it to Contacts table.


    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles


    2018년 5월 18일 금요일 오전 1:35
    중재자
  • This sounds like an interesting approach, but doesn't seem to fit our scenario and the last disadvantage will make it also impractical since we would have millions of guests.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2018년 5월 18일 금요일 오전 1:40
    중재자
  • That was actually my original idea too what we may want a trigger if we would want to add DB protection and not only application logic.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2018년 5월 18일 금요일 오전 1:42
    중재자
  • Hello Naomi,

    My suggestion: Create a view which Returns the email address for those which must be unique and then create a unique index on that view.

    Example:

    CREATE TABLE dbo.Users (name varchar(100) PRIMARY KEY, email varchar(100));
    GO
    CREATE TABLE dbo.KeyValuePairs ([key] varchar(100), value varchar(100));
    GO
    
    CREATE VIEW dbo.ConditionUnique
        WITH SCHEMABINDING
    AS
        SELECT u.email
        FROM dbo.Users AS U
             INNER JOIN
             dbo.KeyValuePairs AS KVP
                 ON U.name = KVP.value
        WHERE KVP.[key] = 'Unique'    
    GO
        
    CREATE UNIQUE CLUSTERED INDEX IDX_ConditionUnique ON dbo.ConditionUnique (email);
    GO
    
    INSERT INTO dbo.KeyValuePairs VALUES ('Unique', 'Olaf');
    INSERT INTO dbo.KeyValuePairs VALUES ('NonUnique', 'Naomi');
    INSERT INTO dbo.KeyValuePairs VALUES ('Unique', 'Paul');
    GO
    
    -- This works
    INSERT INTO dbo.Users VALUES ('Olaf', 'dummy@dummy');
    INSERT INTO dbo.Users VALUES ('Naomi', 'dummy@dummy');
    GO
    -- This fails
    INSERT INTO dbo.Users VALUES ('Paul', 'dummy@dummy');
    


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    2018년 5월 18일 금요일 오전 6:07
  • Hi Naomi,

    Have you considered TRY…CATCH construct ?

    When the particular duplicate record is inserted into the table, we could throw the specific error message, then exit execution procedure. Here is the simple example for your reference, how to achieve your needs depend on your thought and business rules.

    CREATE TABLE dbo.Party
      (
        ID INT NOT NULL IDENTITY ,
        Name VARCHAR(30) NOT NULL
      ) 
    
    INSERT  INTO dbo.Party
            ( Name )
    VALUES  ( 'Frodo Baggins' ),
            ( 'Luke Skywalker' ),
            ( 'Harry Potter' ) ;
    
    select * from dbo.Party
    	
    alter proc insert_record
    (
    @v_name varchar(50)
    )
    as
    
    begin	
    	begin try
        declare @v_count int 
    	select @v_count=count(*) from dbo.Party where [Name]=@v_name
        if @v_count>0
    	begin
    	raiserror('Multiple records in database', 16, 1)
    	end
    	else
    	begin
    	 insert into dbo.Party values(@v_name)
    	end
    
        end try
        begin catch
        --SELECT  
        --    ERROR_NUMBER() AS ErrorNumber  
        --    ,ERROR_SEVERITY() AS ErrorSeverity  
        --    ,ERROR_STATE() AS ErrorState  
        --    ,ERROR_PROCEDURE() AS ErrorProcedure  
        --    ,ERROR_LINE() AS ErrorLine  
        --    ,ERROR_MESSAGE() AS ErrorMessage;  
    
        end catch
    
    end
    
    EXEC insert_record 'Luke Skywalker'

    Best Regards,

    Will


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 5월 18일 금요일 오전 7:11
    중재자
  • Hi,

    another approach can be :

    The "UniqueEmail" param is itself exactly the fact that exists or not a constraint. Look this example:

    create table t_Settings(
    	SomeSetting int,
    )
    go
    insert into dbo.t_Settings values(4)
    go
    create table t_Contacts(
    	Id int identity primary key,
    	Email varchar(255) not null,
    )
    go
    create view v_Settings
    as
    	select s.*, IsMailUnique = convert(bit, case when u.index_id is null then 0 else 1 end)
    	from dbo.t_Settings s
    	outer apply(select * from sys.indexes where object_id=object_id('t_Contacts') and name ='UniqueEmailSettingsIndex') u
    go
    create procedure p_Settings_Save @SomeSetting int, @IsMailUnique bit
    -- with execute as 'sa' --> Administrative permissions for this procedure
    as
    	-- Standard settings part
    	update dbo.t_Settings set SomeSetting=@SomeSetting
    	
    	-- Constraint part
    	if exists(select * from dbo.v_Settings where IsMailUnique!=isnull(@IsMailUnique, 0))
    		begin
    		if @IsMailUnique=1 exec('create unique index UniqueEmailSettingsIndex on dbo.t_Contacts(Email)')
    		else exec('drop index t_Contacts.UniqueEmailSettingsIndex')
    		end
    go
    select * from dbo.v_Settings --> isMailUnique = 0
    exec dbo.p_Settings_Save 1,1
    select * from dbo.v_Settings --> isMailUnique = 1
    insert into dbo.t_Contacts values('a@b.c')
    begin try insert into dbo.t_Contacts values('a@b.c') end try begin catch select error_message() end catch
    exec dbo.p_Settings_Save 1,0
    select * from dbo.v_Settings --> isMailUnique = 0
    insert into dbo.t_Contacts values('a@b.c')

    marc.


    2018년 5월 18일 금요일 오전 7:32
  • Another method I can think of is by means of INSTEAD OF trigger

    something like this

    CREATE TRIGGER Trg_ContactEmail
    ON Emails
    INSTEAD OF INSERT
    AS
    BEGIN
    
    INSERT INTO Emails
    SELECT *
    FROM INSERTED i
    WHERE NOT EXISTS 
    (
    SELECT 1
    FROM Emails e
    JOIN ContactEmailAddresses ca
    ON ca.EmailID = e.EMailID
    JOIN Contacts c
    ON c.ContactID = ca.ContactID
    WHERE e.Email = i.Email
    AND c.ContactType = 'Guest'
    )
    
    DECLARE @DuplicateEmails varchar(5000)
    
    SET @DuplicateEmails = STUFF((SELECT ',' + Email
    FROM INSERTED i
    JOIN Emails e
    ON e.Email = i.Email
    JOIN ContactEmailAddresses ca
    ON ca.EmailID = e.EMailID
    JOIN Contacts c
    ON c.ContactID = ca.ContactID
    WHERE c.ContactType = 'Guest'
    FOR XML PATH('')),1,1,'')
    
    RAISERROR ('Duplicate emails cant be inserted for ' + @DuplicateEmails,16,1)
    
    
    END

    This will go ahead with valid inserts and blocks the duplicate Guest emails


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오전 8:45
  • Check and index constraints are in the same table, Key constraints are between tables. You want an index constraint between tables. There are three approaches to finagle this in. One, disallow direct access to the table. Two, combine the tables. Three, alter the table's definition based on the other table's setting.

    To disallow direct access to the table you can revoke insert and update on the table, and instead use a stored procedure to do the insert. Conversely, you can allow it, but be absolutely evil and use a trigger (/me shudders at the thought) to decide if the insert should be allowed, or instead throw the emails into a second table that has the constraint. The former is a little inconvenient but probably the proper way to do it, as you are defining a method of insert. The latter hides code, and might encourage trigger usage.

    To combine the tables, you can redesign your tables or use a view to combine them. Redesigning doesn't really help as you would still need to check the setting. Combining the tables in a view that has a constraint ought to work, if it fits your model.

    To alter the table's definition based on the other table, the current table should have three columns. The first is true or false, and the default must be altered whenever changing the setting in the other table. The second is the email address provided by the insert statement. The third is a generated column that will use the statement's email address when true, otherwise null. The unique constraint would then go on this third column.

    Fwiw, in Oracle, a constraint can be enabled or disabled, and it can be validated or not validated. Enabling a constraint means it will enforce it on any new data. Validate means it enforces it on existing data. Meaning, you can define a constraint as enable novalidate, and have it only care about new data. In this case, when setting the setting to false you would disable the constraint, and when it is true, enable novalidate it.






    2018년 5월 18일 금요일 오후 1:12
    중재자
  • I think this idea most closely matches our requirements. If we'll get go ahead with the User Story, that's what I'm planning to implement.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    2018년 5월 18일 금요일 오후 7:00
    중재자
  • I think this idea most closely matches our requirements. If we'll get go ahead with the User Story, that's what I'm planning to implement.

    For every expert, there is an equal and opposite expert. - Becker's Law


    My blog


    My TechNet articles

    Yep

    Based on your current scenario I guess like this might be one of the possible solutions


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    2018년 5월 18일 금요일 오후 7:38