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 doing any joins or indexing, and I don’t
Tag: mysql
Fire a trigger after the update of specific columns in MySQL
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
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 searching google for foreign key constraint, I have just learned
#1072 – Key column ‘role_id’ doesn’t exist in table
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 ‘role_id’
How to run OPTIMIZE TABLE with the least downtime
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 table. This
Complicated MySQL Select query
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
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. Some examples of what
ERROR 1148: The used command is not allowed with this MySQL version
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 option
meta_query, how to search using both relation OR & AND?
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 form, gets displayed in the results?
How to get a count even if there are no results corresponding mysql?
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