Categories
Database Development

Why are my rows deleted on update?

I have 2 tables, quizzes and quizzes_answers. The former contains questions and the latter contains available answers to this question. Adding some rows in the table quizzes_answers with a quiz_id higher than 3 makes these newly created rows disappear instantly. Updating an existing row to set its quiz_id to a value higher than 3 also […]

Categories
Database Development

Database Design For Employee Business Hours

I have a simple employee database (I use mySql): Employee ( id int(10) unsigned NOT NULL AUTO_INCREMENT, name varchar(40) NOT NULL, email_Id varchar(40) NOT NULL, mobile_Number varchar(15) NOT NULL, PRIMARY KEY (id), UNIQUE KEY employee_email_id_unique (email_Id) ) Now I need to store different Business Hours for every employee All Monday, 9am to 5pm All Tuesday, […]

Categories
Database Development

How to make MySQL 8 alert when integer overflow happens during INSERT … ON DUPLICATE KEY?

Let’s consider we have a simple table with auto-incrementing integer ID and some unique column: CREATE TABLE `test` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `value` tinyint DEFAULT NULL, PRIMARY KEY (`id`), UNIQUE KEY `value` (`value`) ); Imagine we inserted into it so many times that ID reached the maximum: INSERT INTO test VALUES (4294967294, […]

Categories
Mastering Development

Query Graph: Expected a valid parent ID to be present for a nested connect on a one-to-many relation. (Prisma2)

I’d like to use an optional one-to-many nested relation field in below Comment model which is commonly used in the post-comment type applications. In the Comment table, there is a parentId field that references back to the Comment table. It is intended to use to point the comment which the comment is replying to. parentId […]

Categories
Mastering Development

How to find events the next day and after midnight in MySQL

the day was divided into four time slots 00:00:00-06:00:00 06:00:00-12:00:00 12:00:00-18:00:00 18:00:00-24:00:00 for each of these time slots I need to search the rows in the database table MySQL between for start hour and end hour 21:00:00-09:00:00 03:00:00-15:00:00 09:00:00-21:00:00 15:00:00-03:00:00 that is (-3h) 00:00:00-06:00:00 (+3h) (-3h) 06:00:00-12:00:00 (+3h) (-3h) 12:00:00-18:00:00 (+3h) (-3h) 18:00:00-24:00:00 (+3h) I’v […]

Categories
Database Development

ERROR 1067 (42000): Invalid default value for ‘uploadedDate’

Hi everyone i am getting an error trying to run the below query ERROR 1067 (42000): Invalid default value for ‘uploadedDate’ CREATE TABLE `filenew` ( `id` int(11) NOT NULL AUTO_INCREMENT /* MYSQL_TYPE_LONG */, `originalFilename` varchar(255) CHARACTER SET utf8 NOT NULL /* MYSQL_TYPE_VARCHAR */, `shortUrl` varchar(255) CHARACTER SET utf8 DEFAULT NULL /* MYSQL_TYPE_VARCHAR */, `fileType` varchar(150) […]

Categories
Database Development

Use generated IDs or meaningful fields in mapping table? (user, permission, user permission)

Given the below tables, what is best practice and why for defining mapping table? Should mapping table consist of user.id, permission.id, or say user.email + permission.permission? Are there any pitfalls to not using IDs? CREATE TABLE `user` ( `id` int NOT NULL AUTO_INCREMENT, `email` varchar(45) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `user_name_UNIQUE` (`email`) ) […]

Categories
Mastering Development

How to group results by a date/time period in MySQL while still using an index?

In MySQL, you can create an index to be used in a query to prevent full tables scans. Only one index can be used. Furthermore, in order to use the index, the field(s) indexed cannot be run through a function (i.e. DATE(), MONTH(), YEAR()) because then the query optimizer won’t know what the result will […]

Categories
Database Development

ERROR 1071 (42000): Specified key was too long; max key length is 1000 bytes

An online optimization tool offered to add indexes. One of the index is successfully created but i cant create the second one. Do you think that indexes going to reduce query run time? Any ideas how can i pass through the error code? SQL Query takes 10.1 secs and got over 380 times execution in […]

Categories
Mastering Development

MYSQL schema for comment system

I have a basic comment system on an app I’m creating with the following table setup CREATE TABLE `meet_comment` ( `id` int(11) NOT NULL AUTO_INCREMENT, `meet_id` int(11) NOT NULL, `user_id` int(11) NOT NULL, `date_created` datetime NOT NULL, `comment` mediumtext NOT NULL, PRIMARY KEY (`id`), KEY `meet_id` (`meet_id`), KEY `user_id` (`user_id`), CONSTRAINT `meet_comment_ibfk_1` FOREIGN KEY (`meet_id`) […]