CODEWITHSUNDEEP
mysqlsqlsql-serveroracle-database
Lokomotywa
Lokomotywa

Reputation: 2844

SQL: Add new Primary Key Column to Database Table

I have a database table with several columns where some of them used to form the primary key together. Now the model changed and a single primary key column, called 'id', gets introduced.

When I alter the table and add the column, the column gets populated in every row with an default value, 0 or NULL, so that it can not be used as a candidate for a new primary key. To achieve that, I have to have a different (numerical) value for 'id' in every row. How can I achieve that, preferably as generic as possible (MySQL, Oracle, SQL Server).

Upvotes: 0

Views: 606

Answers (2)

Pradeep Sb
Pradeep Sb

Reputation: 28

CREATE TABLE Persons (
    ID int IDENTITY(1,1) PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

Might be this will be helpful. The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.

In the example above, the starting value for IDENTITY is 1, and it will increment by 1 for each new record.

Tip: To specify that the "ID" column should start at value 10 and increment by 5, change it to IDENTITY(10,5).

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically):

Upvotes: 0

KeithL
KeithL

Reputation: 5594

Here is a sample of how to add an identity column that populates as a primary key.

create table #t (vals varchar(100))

insert into #t
values('a'),('b'),('c')

alter table #t
add ID int identity primary key

select * from #t

Results:

vals    ID
a   1
b   2
c   3

This is a SQL Server answer. I don't know how the other databases work.

Upvotes: 1

Related Questions