Search code examples
mysqlsqldatatableprimary-key

SQL: How to generate a unique value for a Primary Key

as the title states. I do have the following table:

CREATE TABLE exampleTable(
    ID int NOT NULL,
    Text varchar(255),
    PRIMARY KEY (ID)
);

By inserting Values:

INSERT INTO exampleTable (ID, Text)
VALUES (123, 'Hello World');

Sometimes a value can be added for a Primary Key, which is already in the Table, thereby will give you an Error.

Questions:

  1. By which approach can we ensure that the value of a choosen Primary Key is always unique?

  2. How to generate a Primary Key value, that is always going to be unique.

For 2. Question, possible approach:

//Check if the new Value is already in the Table by
//Iterating and comparing through all the Primary Key Values.
Solution

  • The first answer is simple

    1. The PRIMARY KEY Constraint guarantee that the values are UNIQUE and you get an error if not

    2. is more complicated, you can try to get the max(id) and then increase it either in source code or in a BEFORE INSERT TRIGGER, but in heavy duty servers you can get still errors anyway, that is why we use auto_increment or uuids