Unique key during time period

Asked

Viewed 46 times

-1

My table is constituted as follows:

CREATE TABLE `AgendaVisitas` (
  `id` int NOT NULL AUTO_INCREMENT,
  `title` int DEFAULT NULL,
  `respons` varchar(255) DEFAULT NULL,
  `contact` varchar(20) DEFAULT NULL,
  `title1` varchar(255) DEFAULT NULL,
  `contact1` varchar(20) DEFAULT NULL,
  `start` datetime DEFAULT NULL,
  `end` datetime DEFAULT NULL,
  `DataRegisto` datetime DEFAULT NULL,
  `colaborador` varchar(10) COLLATE utf8_unicode_ci DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=26 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

I need that column title be it unique key for 7 days.

Example:

The week begins on Monday and ends on Sunday. If you register code 525 in the column title On Tuesday of this week, I can only re-register the same code the following week, after Sunday. I want this action every week. I can only register the same code once a week.

How can I do this deed?

mysql
  • 1

    I’m far from the best with database, but in this case do this kind of verification by back-end would not be enough? :]

  • Another possible solution is with the use of triggers in the bank itself.

1 answer

2


If what you want is that for each new line, a 1 week count:

  • Create fields for title, week and year.
  • Set up a single key involving the 3 fields.

Each week has a unique number in the year and to get this number you can use the Mysql WEEK function.

If what you want is to change the table structure periodically, you can create an event in Mysql. Here is a tutorial explaining.

The syntax is basically the following:

CREATE EVENT youreventname
    ON SCHEDULE AT yourtime
    DO yourinstructions

  • OK that’s exactly what I intend. My problem is to have the event check between Monday and Sunday, at the time it is registered, even if it is registered on Friday and the next Monday arrives and check the next week again within that interval. I don’t know if I made myself clear.

  • 1

    On closer examination, it seems my answer does not serve your problem. If what you want is for each new line to have a 1 week count, I suggest you create a field for the week (week), and create the unique key with the fields title and week. On the field week you would keep the number corresponding to that week in the year. You can use the Mysql WEEK function https://www.w3resource.com/mysql/date-and-time-functions/mysql-week-function.php

  • A uk should have Tittle,week and year , because the weeks repeat over the years , only week and title would give chance of collision

  • @JP Rodrigues thank you, that’s exactly what I intended

  • Thanks! I edited the answer to better match the question.

Browser other questions tagged mysql

You are not signed in. Login or sign up in order to post.