CODEWITHSUNDEEP
mysqlsql-serverpdo
Mike
Mike

Reputation: 12819

Alter SQL Server Column in PDO

I am trying to make an existing ID column in a SQL Server database AUTO_INCREMENT. I do not have access to the GUI.

I am trying this query.

$stmt = $this->conn()->prepare('ALTER TABLE myTable ALTER COLUMN ID int NOT NULL auto_increment');
if($stmt->execute()){

}else{
    echo "<pre>";
    print_r($stmt->errorInfo());
    echo "</pre>";
}

This gives the following error [2] => Incorrect syntax near 'auto_increment'. [20018] (severity 5) [(null)]

Thanks for the help.

Upvotes: 0

Views: 426

Answers (1)

HLGEM
HLGEM

Reputation: 96590

Well that code certainly won't work on SQL Server where you can't alter table to add an identity functionality to a field after the table is created.

And it wouldn't work even if it could because Identity is what you use on SQL Server no autoincrement.

You need to add the identity as a separate column or do what SSMS does and create a new table with the identity, move the data and then drop the old table and rename the new one.

I can't speak for how to do it in mysql.

Incidentally ID is a horrible name for a PK field. Please use tablenameID. You will avoid alot of accidental bad joins and make reporting much easier.

Upvotes: 2

Related Questions