How to restrict the value of one column with respect to another in a table in SQL?

Question

How to restrict the value of one column with respect to another in a table in SQL?

Navigation

#1 by (0 votes)
#2 by (0 votes)

3

I have the following table:

Clientes (
    Nombre varchar(50) not null,
    Id_Cliente varchar(15) primary key,
    Telefono varchar(15) not null unique,
    Edad int not null,
    Email varchar(50) not null unique,
    Deudor bit not null default 0,
    Deuda real not null default 0
    constraint CK_VALIDACION_EDAD check (Edad >= 18)
)

What I want to achieve is that the "Debt" column only admit data greater than zero if the "Debtor" column is different from zero. How can I achieve this?

Thanks in advance.

sql-server

asked by Jahir Garcia 18.04.2018 в 03:20

source

2 answers

0

You must add a constraint to your table on the Debt field, indicating the condition that you quote in your question, as follows:

CREATE TABLE Clientes (
    Nombre varchar(50) not null,
    Id_Cliente varchar(15) primary key,
    Telefono varchar(15) not null unique,
    Edad int not null,
    Email varchar(50) not null unique,
    Deudor bit not null default 0,
    Deuda real not null default 0,
    constraint CK_VALIDACION_EDAD check (Edad >= 18),
    constraint CK_VALIDACION_Deuda check ((Deuda >= 0) and Deudor != 0)
)

--Ejemplo con deudor igual 0
INSERT INTO Clientes Values ('Delcio', '00115252362', '102-320-3202', 18, '[email protected]', 0, 1500);
--Ejemplo con deudor igual mayor a 0
INSERT INTO Clientes Values ('Delcio', '00115252362', '102-320-3202', 18, '[email protected]', 1, 1500);

To see the live example, view the following fiddle

answered by 18.04.2018 в 03:56

MySQL Query with a while and another query inside Capture data that are in foreach in spring

score 0 · Accepted Answer

The constraint suitable to verify what you ask should be something like this:

CONSTRAINT check_Deuda CHECK ((Deudor = 1) or (Deudor = 0 and Deuda = 0))

For example:

create table Clientes (
    Nombre varchar(50) not null,
    Id_Cliente varchar(15) primary key,
    Telefono varchar(15) not null unique,
    Edad int not null,
    Email varchar(50) not null unique,
    Deudor bit not null default 0,
    Deuda real not null default 0,
    constraint CK_VALIDACION_EDAD check (Edad >= 18),
    CONSTRAINT check_Deuda CHECK ((Deudor = 1) or (Deudor = 0 and Deuda = 0))
)

-- No deudor con Deuda -> Error
insert into Clientes (Nombre, Id_Cliente, Telefono, Edad, Email, Deudor, Deuda) 
values ('cliente 1', 1, '', 19, '', 0, 100);

-- No deudor sin Deuda -> Ok
insert into Clientes (Nombre, Id_Cliente, Telefono, Edad, Email, Deudor, Deuda) 
values ('cliente 2', 2, '', 19, '', 0, 0);

-- Deudor con Deuda -> Ok
insert into Clientes (Nombre, Id_Cliente, Telefono, Edad, Email, Deudor, Deuda) 
values ('cliente 3', 3, '', 19, '', 1, 100);

-- Deudor sin  Deuda -> Ok
insert into Clientes (Nombre, Id_Cliente, Telefono, Edad, Email, Deudor, Deuda) 
values ('cliente 4', 4, '', 19, '', 0, 0);