Mysql: How to truncate a foreign key constrained table?

Why doesn't a TRUNCATE on mygroup work? Even though I have ON DELETE CASCADE SET I get:

ERROR 1701 (42000): Cannot truncate a table referenced in a foreign key constraint (mytest.instance, CONSTRAINT instance_ibfk_1 FOREIGN KEY (GroupID) REFERENCES mytest.mygroup (ID))

drop database mytest;
create database mytest;
use mytest;

CREATE TABLE mygroup (
   ID    INT NOT NULL AUTO_INCREMENT PRIMARY KEY
) ENGINE=InnoDB;

CREATE TABLE instance (
   ID           INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
   GroupID      INT NOT NULL,
   DateTime     DATETIME DEFAULT NULL,

   FOREIGN KEY  (GroupID) REFERENCES mygroup(ID) ON DELETE CASCADE,
   UNIQUE(GroupID)
) ENGINE=InnoDB;

 Answers

You cannot TRUNCATE a table that has FK constraints applied on it (TRUNCATE is not the same as DELETE).

To work around this, use either of these solutions. Both present risks of damaging the data integrity.

Option 1:

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to nowhere
4. Create constraints

Option 2: suggested by user447951 in their answer

SET FOREIGN_KEY_CHECKS = 0; 
TRUNCATE table $table_name; 
SET FOREIGN_KEY_CHECKS = 1;

---

I would simply do it with :

DELETE FROM mytest.instance;
ALTER TABLE mytest.instance AUTO_INCREMENT = 1;

---

you can do

DELETE FROM `mytable` WHERE `id` > 0

---

Option 1:

Option 1: which does not risk damage to data integrity:

1. Remove constraints
2. Perform TRUNCATE
3. Delete manually the rows that now have references to nowhere
4. Create constraints

The tricky part is Removing constraints, so I want to tell you how, in case someone needs to know how to do that:

1. Run SHOW CREATE TABLE <Table Name> query to see what is your FOREIGN KEY's name (Red frame in below image):
2. Run ALTER TABLE <Table Name> DROP FOREIGN KEY <Foreign Key Name>. This will remove the foreign key constraint.
3. Drop the associated Index (through table structure page), and you are done.

to re-create foreign keys:

ALTER TABLE <Table Name>
ADD FOREIGN KEY (<Field Name>) REFERENCES <Foreign Table Name>(<Field Name>);

Rounding numbers with MySQL

MySQL has a number of ways of rounding numbers with the CEILING() FLOOR() ROUND() and TRUNCATE() functions.

ROUND()

ROUND() takes two arguments. The first is the number to round and the second optional argument the number of decimal places to round the number to. If the second argument is not specified then it defaults to 0 thus rounding to the nearest integer. If the part to be rounded is 5 or higher, the number is rounded up otherwise it is rounded down.

Here are some examples, with the result after the SELECT part in a comment:

SELECT ROUND( 1 );   /* = 1 */
SELECT ROUND( 1.4 ); /* = 1 */
SELECT ROUND( 1.5 ); /* = 2 */

SELECT ROUND( -1.4 ); /* = -1 */
SELECT ROUND( -1.5 ); /* = -2 */

SELECT ROUND( 1.4212, 1 ); /* = 1.4 */
SELECT ROUND( 1.4512, 1 ); /* = 1.5 */

CEILING()

CEILING() always rounds the number up to the nearest integer. There is no argument for precision so it always returns an integer. CEIL() is an alias for CEILING() so can also be used as the function name. Here are some examples:

SELECT CEILING( 1 );   /* = 1 */
SELECT CEILING( 1.4 ); /* = 2 */
SELECT CEILING( 1.6 ); /* = 2 */

SELECT CEILING( -1.4 ); /* = -1 */
SELECT CEILING( -1.6 ); /* = -1 */

FLOOR()

FLOOR() works in the same way as CEILING() but always rounds the number down and also has no argument for the precision. Here are some examples:

SELECT FLOOR( 1 );   /* = 1 */
SELECT FLOOR( 1.4 ); /* = 1 */
SELECT FLOOR( 1.6 ); /* = 1 */

SELECT FLOOR( -1.4 ); /* = -2 */
SELECT FLOOR( -1.6 ); /* = -2 */

TRUNCATE()

TRUNCATE() takes two arguments: the number to be truncated and the number of decimal places to truncate to. The second argument is required and MySQL will produce an error message if it is not specified.

The numbers after the specified number of decimal places are truncated from the result. If the decimal places is a negative number then the numbers to the left of the decimal place are truncated.

Some examples:

SELECT TRUNCATE( 1, 0 );       /* = 1    */
SELECT TRUNCATE( 1.5555, 0 );  /* = 1    */
SELECT TRUNCATE( 1.5555, 1 );  /* = 1.5  */
SELECT TRUNCATE( -1.5555, 0 ); /* = -1   */
SELECT TRUNCATE( -1.5555, 1 ); /* = -1.5 */

SELECT TRUNCATE( 12345, -1 );  /* = 12340 */
SELECT TRUNCATE( 12345, -2 );  /* = 12300 */
SELECT TRUNCATE( 12345, -3 );  /* = 12000 */

A final note

Although the examples here show constants in the function calls you would obviously normally be selecting a field with the function call. I've used constants in the examples to show what the result of the function call would be. For example, you'd be more likely to do something like this: "SELECT ..., ROUND(some_field, 2), ... FROM ..."

Related posts:

• Rounding numbers with PHP
• Rounding numbers with Javascript
• Randomly ordering a MySQL result set
• String concatenation with MySQL
• Using SELECT REPLACE with MySQL
• Upper case and lower case strings with MySQL

Mysql: How to set AUTO_INCREMENT step

The AUTO_INCREMENT attribute is used to generate a unique value for an identity column.

Let us check how it works:

-- Create table 
CREATE TABLE employees(
  id INT(11) AUTO_INCREMENT,
  name VARCHAR(30),
  PRIMARY KEY (id)
);
-- Add some records
INSERT INTO employees (name)
  VALUES ('John'), ('Angela'), ('George'), ('Kate');
-- Retrieve data
SELECT id, name FROM employees;
+----+--------+
| id | name   |
+----+--------+
|  1 | John   |
|  2 | Angela |
|  3 | George |
|  4 | Kate   |
+----+--------+

As you can see id values were generated from 1 to 4 with step 1. To control AUTO_INCREMENT operation we can use server variables -auto_increment_increment и auto_increment_offset.

• auto_increment_increment - is the incremental value, controls the interval between successive column values.
• auto_increment_offset - determines the starting value for the AUTO_INCREMENT field; this value is used for the first record inserted into the table.

These variables have session and global scope.

Let us try to use these variables -

-- Empty table
TRUNCATE TABLE employees;
-- Set variables (SESSION scope)
SET @@session.auto_increment_increment = 10;
SET @@session.auto_increment_offset = 5;
-- Add some records
INSERT INTO employees (name)
  VALUES ('John'), ('Angela'), ('George'), ('Kate');
-- Retrieve data
SELECT id, name FROM employees;
+----+--------+
| id | name   |
+----+--------+
|  5 | John   |
| 15 | Angela |
| 25 | George |
| 35 | Kate   |
+----+--------+

The first records has id = 5, next id is 15 and so on.