This is likely because you have named at least one constraint with the same identifier as a column: /* You already have a column named `restaurant` in this table, but are naming the FK CONSTRAINT `restaurant` also… */ CONSTRAINT `restaurant` FOREIGN KEY (`restaurant` ) REFERENCES `mydb`.`restaurants` (`id` ) ON DELETE NO ACTION ON UPDATE NO … Read more
I ran into this same problem when installing via homebrew. Make sure you run these commands (which are listed during install but easy to miss): unset TMPDIR mysql_install_db
create_user INT UNSIGNED ZEROFILL cannot reference id INT, because these count as different data types for purposes of foreign key reference. Make them the same data type. The only data type difference that is permitted between columns in a foreign key relationship is length of a varchar. For example, VARCHAR(10) can reference VARCHAR(20) or vice-versa. … Read more
If you have different fields that have different meaning too, you can’t and shouldn’t return them in the same position. You can however ‘fill in the blanks’ by adding null to your fields, like this: select id, name, date, null as userid, ‘A’ as recordtype from table1 union all select id, name, null /*as date*/, … Read more
This command adds a unique key, and drops all rows that generate errors (due to the unique key). This removes duplicates. ALTER IGNORE TABLE table ADD UNIQUE KEY idx1(title); Edit: Note that this command may not work for InnoDB tables for some versions of MySQL. See this post for a workaround. (Thanks to “an anonymous … Read more
There is another solution, if you have binary logs active on your server you can use mysqlbinlog generate a sql file with it mysqlbinlog binary_log_file > query_log.sql then search for your missing rows. If you don’t have it active, no other solution. Make backups next time.
No, a temp table is limited to the scope of your database connection. You can use the temp table in subsequent calls to the procedure during the same database connection, but other connections cannot access it. They can create a table by the same name, but each temp table will be independent. Temp tables go … Read more
This is a problem that is pretty common: generating a relation on the fly without creating a table. SQL solutions for this problem are pretty awkward. One example using a derived table: SELECT n.id FROM (SELECT 2 AS id UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7) AS … Read more
3 months before today: select * from table where timestamp >= now()-interval 3 month; Start with first of month: select * from table where timestamp >= last_day(now()) + interval 1 day – interval 3 month;
DELETE DELETE is a DML Command. DELETE statement is executed using a row lock, each row in the table is locked for deletion. We can specify filters in where clause It deletes specified data if where condition exists. Delete activates a trigger because the operation are logged individually. Slower than truncate because, it keeps logs. … Read more