While it is possible to manually set a primary key value, doing so almost guarantees a key clash at some point. Instead, primary key values should
be set by (in descending order of desirability):
- automatic generation by the database via a column definition such as
PROD_ID INT NOT NULL GENERATED ALWAYS AS IDENTITY (START WITH 1,
INCREMENT BY 1, NO CACHE)
- the
Generate_Unique() function
- a value pulled directly from a sequence, like so:
nextval for SEQ_PRODUCT
This rule raises an issue when an INSERT statement assigns values to identity columns that are configured to always generate their
values.
Note That this rule raises issues only when a database catalog is provided during the SonarQube analysis.
Noncompliant code example
CREATE table my_table (
column_a integer GENERATED ALWAYS AS IDENTITY primary key not null,
column_b varchar(50)
);
INSERT into my_table (column_a, column_b)
VALUES (1, 'Hello World'); -- Noncompliant
