Do not allow 0 when creating column-CodePudding

I am creating a table in SQL Server and I have a column of decimal type that should not receive the value 0, how could I do that?

CREATE TABLE Producto
(
    Idproducto [int] PRIMARY KEY NOT NULL 
        DEFAULT (NEXT VALUE FOR Idprod),
    Nombre [varchar](100) NOT NULL,
    Precio_Unitario DECIMAL (18,2) 
);

CodePudding user response：

You can use a CHECK constraint. For example:

CREATE TABLE Producto
(
    Idproducto int PRIMARY KEY NOT NULL,
    Nombre varchar(100) NOT NULL,
    Precio_Unitario DECIMAL(18, 2),
    CONSTRAINT chk1 CHECK (Precio_Unitario <> 0)
);

Typical inserts will work well:

INSERT INTO Producto (Idproducto, Nombre, Precio_Unitario) 
VALUES (1, 'A', 2);

INSERT INTO Producto (Idproducto, Nombre, Precio_Unitario) 
VALUES (2, 'B', NULL);

But if you try:

INSERT INTO Producto (Idproducto, Nombre, Precio_Unitario) 
VALUES (3, 'C', 0);

You'll get an error:

Msg 547 Level 16 State 0 Line 1
The INSERT statement conflicted with the CHECK constraint "chk1". The conflict occurred in database "fiddle_914c4e0be5e144138dc870cf3d5a6ced", table "dbo.Producto", column 'Precio_Unitario'.

Msg 3621 Level 0 State 0 Line 1
The statement has been terminated.

See running example at db<>fiddle.

CodePudding user response：

You can create a check constraint, that will force all values in that column to be null or different from zero

CREATE TABLE #Producto
(
    Idproducto [int]  PRIMARY KEY NOT NULL DEFAULT (NEXT VALUE FOR Idprod),
    Nombre [varchar](100) NOT NULL,
    Precio_Unitario DECIMAL  (18,2),
    constraint chk_Precio_Unitario check (Precio_Unitario is null or Precio_Unitario <> 0) 
)