Raw query to set AutoIncrement to false?-CodePudding

The id column in the student table is an auto incrementing one.I wanted to make that to non - autoincrementing. May i know, how can i modify the below query to work as such?

 DB::statement("ALTER TABLE student SET AUTO_INCREMENT=FALSE;");

the above code shows the below error.

Illuminate\Database\QueryException

  SQLSTATE[42601]: Syntax error: 7 ERROR:  syntax error at or near "AUTO_INCREMENT"
LINE 1: ALTER TABLE student SET AUTO_INCREMENT=FALSE;
                                ^ (SQL: ALTER TABLE student SET AUTO_INCREMENT=FALSE;)

CodePudding user response：

The correct syntax in PostgreSQL would be:

ALTER TABLE student ALTER COLUMN id DROP DEFAULT;

Where id is the serial column.

You might also want to drop the not null constraint :

ALTER TABLE student ALTER COLUMN id DROP NOT NULL;

CodePudding user response：

This is an example of why posting table definitions (ddl) and Postgres version can be critical. @Zakaria is correct if the Postgres version is prior to version 10, or if version 10 or later and is still defined as serial/bigserial. However the preferred definition for version 10 and later is generated always as identity. If defined as identity you need:

alter table student alter column id drop identity.

I would not drop a not null constraint, and if it is the PK it is moot point as it will automatically be not null.