0

I use a database project in visual studio

I tried to get rid of that error by allowing UNIQUE NULLABLES but it keeps appearing

I want fields to have unique values EXCEPT when they are null

(I know there are many answers but they dont relate to a create table query)

what do I do wrong ?

CREATE TABLE [dbo].[users] ( 
    [Id] INT IDENTITY (1, 1)  NOT NULL PRIMARY KEY,
    [Email]          VARCHAR (100) UNIQUE NONCLUSTERED NULL DEFAULT NULL,
    [Login]          VARCHAR (100) UNIQUE NONCLUSTERED NULL DEFAULT NULL,
    [Identifier] VARCHAR(40) NOT NULL UNIQUE DEFAULT newid(),
    [ResetPasswordToken] VARCHAR(40) UNIQUE NONCLUSTERED NULL DEFAULT   NULL,
    [ResetPasswordDate]DATETIME UNIQUE NONCLUSTERED NULL DEFAULT   NULL,
...(other fields)
);

thanks for your help on this

phil123456
  • 1,192
  • 2
  • 10
  • 26
  • Does this answer your question? [duplicate null value violation on UNIQUE KEY constraint in Mssql](https://stackoverflow.com/questions/31947263/duplicate-null-value-violation-on-unique-key-constraint-in-mssql) – astentx Feb 03 '21 at 16:18
  • no, it's the first answer I read, as I mentioned it is not a create table and my above code is not working – phil123456 Feb 04 '21 at 08:54
  • Why do you try to achieve it in `create table`? The approach in that answer do the same, but outside of table definition (I mean unique is constraint enforced by unique index no matter where it was declared). – astentx Feb 04 '21 at 08:58

1 Answers1

0

the answer is about using the word "GO"

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_User_Email] ON [dbo].[Users]([Email] ASC) WHERE ([Email] IS NOT NULL);

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_User_Login] ON [dbo].[Users]([Login] ASC) WHERE ([Login] IS NOT NULL);

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_User_ResetPasswordToken] ON [dbo].[Users]([ResetPasswordToken] ASC) WHERE ([ResetPasswordToken] IS NOT NULL);

GO
CREATE UNIQUE NONCLUSTERED INDEX [IX_User_ResetPasswordDate] ON [dbo].[Users]([ResetPasswordDate] ASC) WHERE ([ResetPasswordDate] IS NOT NULL);
phil123456
  • 1,192
  • 2
  • 10
  • 26