CODEWITHSUNDEEP
sqlpostgresql
u84six
u84six

Reputation: 4941

Is there a way to prevent a query from setting the serial primary key?

I've got a bunch of tables with the 'serial' keyword on a primary key so that auto-increment will work. The problem is that I can make a query to insert a row using any id number which overrides the auto-increment. Is there a reason for this? Or, is there a way to prevent a user from adding/changing this value?

Here's an example of my table config:

create table if not exists departments (
    department_id serial primary key,
    name varchar(64) not null unique
);

if I run the following query, I can add any number to primary key:

insert into departments (department_id, name) values (9001, 'FooBar')

I think I want to prevent this from happening. I'd like to get some opinions.

Upvotes: 1

Views: 903

Answers (3)

user330315
user330315

Reputation:

Use an identity column:

create table if not exists departments (
    department_id integer primary key generated always as identity,
    name varchar(64) not null unique
);

This will prevent an insert to override the generated value. You can still circumvent that by specifying OVERRIDING SYSTEM VALUE as part of your INSERT statement. But unless you specify that option, providing a value for the column will result in an error.

Related: PostgreSQL: serial vs identity

Upvotes: 3

Vinod Sherikar
Vinod Sherikar

Reputation: 9

I'm using Microsoft sql server and you can control the insert of value in identity column by below sql command

SET IDENTITY_INSERT [ [ database_name . ] schema_name . ] table_name { ON | OFF }

By setting it to 'OFF' you cannot insert a value in identity column.

For more info refer: https://learn.microsoft.com/en-us/sql/t-sql/statements/set-identity-insert-transact-sql?view=sql-server-ver15

Upvotes: -1

Isaí Hinojos
Isaí Hinojos

Reputation: 172

Unless '9001' isn't already in the registers, it shouldn't cause any trouble. If the filed 'department_id' is already auto increment you can just run your insert statement like 

INSERT INTO departments (name) VALUES ('FooBar')

Upvotes: 0

Related Questions