CODEWITHSUNDEEP
sqlsql-server
Coding Academy
Coding Academy

Reputation: 73

Unique constraint on another column if not null

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

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

Upvotes: 4

Views: 1794

Answers (3)

sniperd
sniperd

Reputation: 5274

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

Upvotes: 2

Richardissimo
Richardissimo

Reputation: 5763

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

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

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.

Upvotes: 1

Related Questions