1

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.

u84six
  • 4,604
  • 6
  • 38
  • 65
  • Is 'serial' the name of the table row or the value that is in the cell?, if it's defined as primary key and you try to insert an existing number it'll throw an error. – Isaí Hinojos Feb 18 '20 at 17:05
  • 'serial' is a keyword in postgresql to allow auto increment. I'll update my question with more info. – u84six Feb 18 '20 at 17:08
  • What version of postgresql are you running? – Barry Piccinni Feb 18 '20 at 17:16
  • @BarryPiccinni I think it's 11. It's aws RDS and can't seem to find the full version number using their portal. – u84six Feb 18 '20 at 17:20
  • 2
    There are a few approaches to do this. One method I've used is to create a trigger that updates the column with the next value in the sequence, no matter what you insert. You can read about that here: https://dba.stackexchange.com/questions/116054/prevent-explicit-insert-into-serial-columns – Barry Piccinni Feb 18 '20 at 17:35
  • @BarryPiccinni I'm starting to think that PG doesn't have this feature for a reason. For now, I'll just fix the api so that it doesn't allow id to be inserted/updated. – u84six Feb 18 '20 at 17:47
  • You can hide the table behind a view or create a trigger to do this. – clamp Feb 18 '20 at 18:17

3 Answers3

3

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

0

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')
Isaí Hinojos
  • 172
  • 2
  • 11
  • As per your question if there's a way users can change it, unless the user has direct access to the registers or the insert statement, the filed will keep on increment with no trouble. – Isaí Hinojos Feb 18 '20 at 17:15
  • is there a way to configure the table so that a user can't add their own id? – u84six Feb 18 '20 at 17:17
-1

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

Vinod Sherikar
  • 9
  • 3
  • You cannot insert value for same primary key more than once. – Vinod Sherikar Feb 19 '20 at 04:00
  • Check out this link. It suggests solutions to control explicit insert in serial column of postgres sql server https://www.google.com/url?sa=t&source=web&rct=j&url=https://dba.stackexchange.com/questions/116054/prevent-explicit-insert-into-serial-columns&ved=2ahUKEwjHsd2w6NznAhWhguYKHRDJBLAQjjgwAHoECAMQAQ&usg=AOvVaw36mSjAFnxPF6YCINuIBe9k – Vinod Sherikar Feb 19 '20 at 04:57