Question 21442 in Mysql Question posted in
The official MySQL documentation can be found here.

Disable manually setting auto-incremented ids in MySQL

I want to disallow users from inserting into a table product (which has auto-incremented ids) if they’re setting the id manually.

So this would be valid (id generated automatically since it’s set as AUTO_INCREMENT):

INSERT INTO product (name) VALUES ("Product1")

But this wouldn’t (id being set manually):

INSERT INTO product (id, name) VALUES (10, "Product1")

Is this possible in any way?

Tags:

2

Answers


  1. 0

    Trigger logic may help.

    SET SESSION sql_mode := '';

CREATE TABLE test (
  id INT AUTO_INCREMENT PRIMARY KEY,
  val INT
) AUTO_INCREMENT = 123;

    CREATE TRIGGER fail_explicit_id
BEFORE INSERT ON test
FOR EACH ROW
BEGIN
  IF NEW.id <> 0 THEN
    SIGNAL SQLSTATE '45000'
      SET MESSAGE_TEXT = 'Explicit ''id'' value is not allowed.';
  END IF;
END

    INSERT INTO test (val) VALUES (111);
INSERT INTO test VALUES (NULL, 222);
INSERT INTO test VALUES (0, 333);
INSERT INTO test VALUES (DEFAULT, 444);

    INSERT INTO test VALUES (456, 555);

    Explicit 'id' value is not allowed.

    SET SESSION sql_mode := CONCAT_WS(',', @@sql_mode, 'NO_AUTO_VALUE_ON_ZERO');
SELECT @@sql_mode;
INSERT INTO test VALUES (0, 666);

    INSERT INTO test VALUES (0, 777);

    Duplicate entry '0' for key 'test.PRIMARY'

    SELECT * FROM test;
    id val
    0 666
    123 111
    124 222
    125 333
    126 444

    fiddle

    Login or Signup to reply.
  2. 0

    Give the user column-level permissions.

    grant insert(`name`) on yourdatabase.product to theuser@thehost;

    Then the user tries these:

    mysql> INSERT INTO product (name) VALUES ("Product1");
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO product (id, name) VALUES (10, "Product1");
ERROR 1143 (42000): INSERT command denied to user 'theuser'@'thehost' for column 'id' in table 'product'
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search