1

I have the following table schema:

CREATE TABLE saved_custom_view (
[saved_custom_view_id]  BIGINT          IDENTITY (1, 1) NOT NULL,        
[user_id]               BIGINT          NULL,   
[name]                  NVARCHAR (250)  NOT NULL,
[is_default_view]       BIT             NOT NULL DEFAULT 0
CONSTRAINT [PK__saved_custom_view] PRIMARY KEY CLUSTERED ([saved_custom_view_id] ASC),    
CONSTRAINT [FK__saved_custom_view__user] FOREIGN KEY ([user_id]) REFERENCES [user] ([user_id]),

);

I want to make sure that no user has more than one row with is_default_value set to true. Therefore, the second insert here should be prevented with a proper CHECK CONSTRAINT. 

insert into saved_custom_view values (1, 'test', 1)
insert into saved_custom_view values (1, 'test', 1)

How do I write a check constraint for this?

ALTER TABLE saved_custom_view
ADD CONSTRAINT CK_no_user_has_duplicate_default_views CHECK (
    -- what goes here?
);

Or is there a way to do a unique check against multiple columns?

Adam Levitt
  • 10,316
  • 26
  • 84
  • 145

1 Answers1

3

You can just use a filtered unique index:

create unique index idx_save_custom_view_u1 on
    saved_custom_view(user_id, is_default_value)
    where is_default_value = 1;

These are described in more detail here.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786