Mysql - check constraint with user input

Question

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.

Álvaro González · Accepted Answer · 2020-05-01 17:43:57Z

1

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.

answered May 1, 2020 at 17:43

Álvaro González

147k45 gold badges282 silver badges378 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Mysql - check constraint with user input

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related