When I try to insert a NULL into a column from my table, it gives me error of violation and I can't understand why. As you see, I have already put into the table one NULL type but it's from a different row, with a different ID and a different name. Please check the screenshot below:
| id | nif | nome | morada |
|---|---|---|---|
| 1 | 213 | aaaa | gggggg |
| 2 | NULL | bbbb | xxxxxx |
| 3 | 215 | cccc | qwqwdq |
This is the query that I'm using to try to insert another row of data, similar with the id -> 2
column:
INSERT INTO Cliente (id, nome) VALUES ('5', N'Arnaldo Magalhães')
This is the query I used to create the table on the screenshot:
CREATE TABLE [dbo].[Cliente](
[id] [int] NOT NULL,
[nif] [int] NULL,
[nome] [varchar](50) NOT NULL,
[morada] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
)
That's the DDL:
USE [Restaurante]
GO
/****** Object: Table [dbo].[Cliente] Script Date: 6/17/2022 12:14:11 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Cliente](
[id] [int] NOT NULL,
[nif] [int] NULL,
[nome] [varchar](50) NOT NULL,
[morada] [varchar](50) NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
UNIQUE NONCLUSTERED
(
[nif] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
And that's the error:
Violation of UNIQUE KEY constraint 'UQ__Cliente__DF97D0F220E9258E'. Cannot insert duplicate key in object 'dbo.Cliente'. The duplicate key value is (NULL).
