CODEWITHSUNDEEP
mysql
DanielS
DanielS

Reputation: 25

Mysql - check constraint with user input

I have the following table

create table consultas(
    id_consultas int(3) primary key auto_increment,
    id_med int(3) not null,
    num_cli int(3) not null,
    id_tipo int(3) not null,
    id_sala int(3) not null,
    data_inicio datetime not null,
    date_fim datetime,
    id_promocao
)

The idea for this table is to save the clinic records of appointments ("consulta")

And i wanted to make a Check Constraint.

So that every time i try to add a new entry it checks if there is already a "consulta" for that client and at that time (data_inicio).

My problem is how do I represent those values in the Check Constraint.

I think it should look something like this

CONSTRAINT CHK_constraint1 CHECK ((SELECT COUNT(*) FROM consultas WHERE data_inicio = ??? AND num_cli = ?) = 0)

Is this possible to achieve or does it have to be the software using the database that makes this check.

If you have any tips please tell me since i don't have much experience with databases.

Upvotes: 0

Views: 137

Answers (1)

Álvaro González
Álvaro González

Reputation: 146430

What you need is a unique index or unique constraint on num_cli and data_inicio. A check constraint is used to impose restrictions in cell values (e.g., ensure a number is multiple of 5 or force a string to be uppercase).

Beware, though, that while the index itself is not a bad idea I won't necessarily solve your problems because you can't possibly assign appointments at 12:00:00 and 12:00:01.

Upvotes: 1

Related Questions