Tag: mysql

Can I use VARCHAR as the PRIMARY KEY?

I have a table for storing coupons/discounts, and I want to use the coupon_code column as the primary key, which is a VARCHAR. My rationale is that, each coupon will have a unique code, and the only commands I will be running are SELECT … FROM … WHERE coupon_code=’..’ I won’t be …

Fire a trigger after the update of specific columns in MySQL

mysql sql triggers

In MySQL, I want to fire a trigger after the update of specific columns. I know how to do it in Oracle and DB2: CREATE TRIGGER myTrigger AFTER UPDATE of myColumn1,myColumn2 … ON myTable FOR EACH …

MySQL : ERROR 1215 (HY000): Cannot add foreign key constraint

database mysql sql

I have read Database system concepts, 6th edition, Silberschatz. I’m going to implement the university database system shown in chapter 2 on OS X on MySQL. But I have a trouble with creating the table course. the table department looks like Creating the table course causes the following error. after sea…

#1072 – Key column ‘role_id’ doesn’t exist in table

mysql sql

I am trying to add foreign key on my other table but this gave me error #1072 – Key column ‘role_id’ doesn’t exist in table I have created a table named role then I created like this then when I try to alter table on my user table and I got an error like this #1072 – Key column &…

How to run OPTIMIZE TABLE with the least downtime

mysql optimization sql

I have a MySQL 5.5 DB of +-40GB on a 64GB RAM machine in a production environment. All tables are InnoDB. There is also a slave running as a backup. One table – the most important one – grew to 150M rows, inserting and deleting became slow. To speed up inserting and deleting I deleted half of the …

Complicated MySQL Select query

mysql relational-database sql

Here is my table layout: What I would like to do: Count the number of votes for each songID in a particular room (passed in as a parameter). Find out if the userID has at least one vote for a song in a particular room. UserID is also passed in as a parameter. An extension of the two above. If

Query to check if date is between two month-day values

date mysql sql

I want to know if a date (Year-Month-Day) is between a day of the month (Month-Day). For example, I want to know if ‘April 2, 2013’ is between ‘April 2’ and ‘April 19’. I can easily do this of the range has a year value. Howver, without a year, I need ideas how to do this. …

ERROR 1148: The used command is not allowed with this MySQL version

import mysql sql

I am trying to load data into mysql database using the topic of this question is the response I get. I understand the local data offloading is off by default and I have to enable it using a the command local-infile=1 but I do not know where to place this command. Answer You can specify that as an additional o…

meta_query, how to search using both relation OR & AND?

meta-query mysql sql wordpress

Resolved: See answer below. I have a custom post type called BOOKS. It has several custom fields, named: TITLE, AUTHOR, GENRE, RATING. How do I fix my meta_query code below so that only books that have the search word in the custom fields: title, author, genre WITH EXACTLY the rating specified in my search fo…

How to get a count even if there are no results corresponding mysql?

mysql sql

I am formalating a query to give the number of reports submitted over the last year ordered by date. I get the current year and month with php: and execute the following query: SQL: And because there has only been 1 submitted in the last year it gives me only 1 result… But I would like the result set to