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 trigger to force business rules constraints. — JohnyL
– JohnyL, Commented Jul 26, 2018 at 19:46
so you can have 2 JohnSmith usernames for productID 1 as long as both have a DeletedAt value? — dfundako
– dfundako, Commented Jul 26, 2018 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
– M.Ali, Commented Jul 26, 2018 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
– Brian, Commented Jul 26, 2018 at 20:03

d219 · Accepted Answer · 2024-01-26 21:58:03Z

2

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,
    [deletedat] [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 ([DELETEDAT] 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 :)

edited Jan 26, 2024 at 21:58

d219

2,8757 gold badges34 silver badges39 bronze badges

answered Jul 26, 2018 at 20:04

sniperd

5,2846 gold badges32 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Coding Academy Over a year ago

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 Over a year ago

I just updated my question with the create table statement. Maybe I made some changes that did some unexpected

sniperd Over a year ago

@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 :)

Gordon Linoff · Accepted Answer · 2018-07-27 01:17:15Z

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, 2018 at 1:17

answered Jul 26, 2018 at 20:00

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

2 Comments

Coding Academy Over a year ago

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?

Gordon Linoff Over a year ago

@CodingAcademy . . . This works fine on SQL Fiddle: sqlfiddle.com/#!18/1f357.

Richardissimo · Accepted Answer · 2018-07-27 03:33:30Z

1

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...

edited Jul 27, 2018 at 3:33

answered Jul 26, 2018 at 20:00

Richardissimo

5,8352 gold badges24 silver badges41 bronze badges

Collectives™ on Stack Overflow

Unique constraint on another column if not null

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related