Estimated hours using a check

Question

Estimated hours using a check

Navigation

#1 by (0 votes)

2

I have a table in which I have three fields which are:

create table horas
(
    fechaEntrada datetime,
    fechaSalida datetime,
    horasEstimadas int
);

and I have to make a restriction in the table that is, that the estimated hours should not be greater than the hours on the date of entry and the date of departure.

Try a CHECK and in with a DATEDIFF but it always marks me wrong.

horasEstimadas int check (horasEstimadas <= (select DATEDIFF(DD, fechaEntrada, fechaSalida)))

And he sends me this error:

Subqueries are not allowed in this context. Only scalar expressions are allowed.

I'm using SQL SERVER 2008.

I do not know what to do, thank you in advance for reading.

sql sql-server-2008

asked by Thunder 22.08.2017 в 03:54

source

1 answer

how to use plt.xticks () to show dates (in days) in python? cx_Freeze, "ImportError DLL load failed"

score 0 · Accepted Answer

There are some problems that I see with your creation statement: the first one tells you the error, is that you can not use a subquery as part of a CHECK which is what you are doing when using a SELECT , for other side you do a DATEDIFF per DD that are days and you compare them with a field that seems to be hours, and finally, the biggest problem is that you can not implement a CHECK at the column level using data from other columns, that is, you can implement something like horasEstimadas int check (horasEstimadas <= 24) but do not use other columns in the verification. To do that you must implement a CONSTRAINT at the row level, which is triggered when the INSERT/UPDATE is completed, in short your sentence would be like this:

create table horas
(
    fechaEntrada datetime,
    fechaSalida datetime,
    horasEstimadas int,

    CONSTRAINT chk_horasEstimadas CHECK (horasEstimadas <= DATEDIFF(hh, fechaEntrada, fechaSalida))
);

Note, that you replace DD with HH in DATEDIFF