Unique constraint on another column if not null

Question

I want to create this table with the following constrains, is it possible to do with SQL Server Management Studio?

Id
ProductId [Nullable column]
CompanyId [Nullable column]
Username [unique for every productId - IF DeletedAt is not NULL]
Identifier [Nullable column] [unique for every companyId - IF DeletedAt is not NULL]
DeletedAt [Nullable column]

Update: My table create query is the following:

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[User](
    [Id] [int] IDENTITY(1,1) NOT NULL, 
    [ProductId] [int] NULL,
    [CompanyId] [int] NULL,
    [Username] [nvarchar](max) NOT NULL,
    [Identifier] [nvarchar](max) NULL,
    [DeletedAt] [datetime2](7) NULL,
 CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, 
ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

I guess you would you [INSTEAD OF](https://learn.microsoft.com/en-us/sql/t-sql/statements/create-trigger-transact-sql) trigger to force business rules constraints. — JohnyL, Jul 26 '18 at 19:46
so you can have 2 JohnSmith usernames for productID 1 as long as both have a DeletedAt value? — dfundako, Jul 26 '18 at 19:54
You can create these constraints but with such a complex logic in constraints, you will get a performance hit, I would suggest to use Stored Procedures (for updates, inserts and deletes) to handle the business logic and do not use such convoluted constraints. — M.Ali, Jul 26 '18 at 19:59
Is "DeletedAt" a DateTime, by any chance? When I read the requirements my first thought was "that's a slowly changing dimension and he's using 'DeletedAt' as a logical deletion flag." If I'm right, you can just add DeletedAt to the unique constraint. Alternately, you could make an archival table and whenever you delete a record from your main table you make a copy of it in your archive table. — Brian, Jul 26 '18 at 20:03
@JohnyL Could you be more specific and give me an example of this trigger just to help me out? — Coding Academy, Jul 26 '18 at 20:03
Possible duplicate of [conditional unique constraint](https://stackoverflow.com/questions/866061/conditional-unique-constraint) — Vinit, Jul 26 '18 at 20:04
@Brian yes its a datetime2 datatype. Would it really work? Because DeletedAt is not unique, only if null.. I cant seem to work it out in my head if it will work. — Coding Academy, Jul 26 '18 at 20:07
I assumed that "DeletedAt" is being set programmatically, so it will be unique as a practical matter unless you manage to delete multiple records at precisely the same moment (down to the millisecond). That said, a filtered index might be a better way to go. — Brian, Jul 26 '18 at 20:16
@Brian Okay thanks for your help. i'm looking into the filtered indexes as being proposed as answers. — Coding Academy, Jul 26 '18 at 20:28

score 2 · Answer 1 · answered Jul 26 '18 at 20:04

You'll want to use a unique filtered index. Here you go for the table:

/****** Object:  Table [dbo].[the_table]    Script Date: 7/26/2018 4:04:00 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[the_table](
    [id] [int] NOT NULL,
    [productid] [varchar](50) NULL,
    [companyid] [varchar](50) NULL,
    [username] [varchar](50) NULL,
    [identifier] [varchar](50) NULL,
    [deleteat] [varchar](50) NULL,
 CONSTRAINT [PK_the_table] PRIMARY KEY CLUSTERED 
(
    [id] 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

And the index for username:

SET ANSI_PADDING ON
GO

/****** Object:  Index [UIDX_USERNAME]    Script Date: 7/26/2018 4:03:31 PM ******/
CREATE NONCLUSTERED INDEX [UIDX_USERNAME] ON [dbo].[the_table]
(
    [username] ASC
)
WHERE ([DELETEAT] IS NOT NULL)
WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO

It would be the same index logic for the other column, but I don't want to clutter this answer by posting more! I would not mess around with putting the logic in triggers or stored proc's, bad data could still sneak in. If you keep nice constraints, nobody can clutter your data up :)

Hi. I get the following error when trying to do the index: Column 'Username' in table 'dbo.User' is of a type that is invalid for use as a key column in an index. — Coding Academy, Jul 26 '18 at 20:46
I just updated my question with the create table statement. Maybe I made some changes that did some unexpected — Coding Academy, Jul 26 '18 at 20:51
@CodingAcademy I don't think you can have a index on a `VARCHAR(MAX)` data type. What you _could_ do is change it to `VARCHAR(8000)` or something more reasonable, like maybe 100. I doubt you'd have a username that is really that big :) — sniperd, Jul 27 '18 at 03:10

Richardissimo · Answer 2 · 2018-07-27T03:33:30.010

Create a unique index on ProductId and Username, and use a where clause to restrict that index to WHERE DeletedAt IS NOT NULL. Answer showing an example of a unique filtered index.

You may also need the clause AND ProductId IS NOT NULL, although that wasn't stated as a requirement, but since that column is nullable you ought to think about this.

Similar for Identifier.

You might also want to think about the value of a table where it is valid to have a row where all the columns except Id are null...

Gordon Linoff · Answer 3 · 2018-07-27T01:17:15.940

1

You can use filtered indexes. The syntax looks like this:

create unique index unq_thetable_username on the_table(username)
    where deleteAt is not null;

I don't know if you can point-and-click your way to this solution. I would just write the logic as above.

edited Jul 27 '18 at 01:17

answered Jul 26 '18 at 20:00

Gordon Linoff

1,242,037
58
646
786

I get an error with your syntax: Column 'Username' in table 'dbo.User' is of a type that is invalid for use as a key column in an index. Is it because its not nullable? – Coding Academy Jul 26 '18 at 20:55
@CodingAcademy . . . This works fine on SQL Fiddle: http://www.sqlfiddle.com/#!18/1f357. – Gordon Linoff Jul 27 '18 at 01:17

Unique constraint on another column if not null

3 Answers3