Обсуждение: Need help to implement Primary Key.
Hi,
I have a table with a record count of ~500 million (496,098,390). There is no primary key in this table. Now I need to set up a primary key on this table. I can't find a way to set up a primary key. I tried to add a new column with IDENTITY but it is not allowing null value for existing rows and I don't know how to update the unique value for existing records.
Please help me to implement the same.
Please help me to implement the same.
Thanks & Regards
Gambhir Singh
Gambhir Singh
Gambhir Singh schrieb am 08.02.2024 um 11:30: > I have a table with a record count of ~500 million (496,098,390). > There is no primary key in this table. Now I need to set up a primary > key on this table. I can't find a way to set up a primary key. I > tried to add a new column with IDENTITY but it is not allowing null > value for existing rows and I don't know how to update the unique > value for existing records. If you add an identity column, it will automatically be populated alter table foo add id bigint generated always as identity not null; Once that has finished (which will take some time given the size of the table), you can add the primary key: alter table foo add constraint pk_foo primary key (id);
Am 08.02.24 um 11:37 schrieb Thomas Kellerer: > Gambhir Singh schrieb am 08.02.2024 um 11:30: > >> I have a table with a record count of ~500 million (496,098,390). >> There is no primary key in this table. Now I need to set up a primary >> key on this table. I can't find a way to set up a primary key. I >> tried to add a new column with IDENTITY but it is not allowing null >> value for existing rows and I don't know how to update the unique >> value for existing records. > If you add an identity column, it will automatically be populated > > > alter table foo > add id bigint generated always as identity not null; > > Once that has finished (which will take some time given the size of the table), > you can add the primary key: > > alter table foo > add constraint pk_foo primary key (id); > You can even do this in one step: alter table table1 add column id bigint primary key generated always as identity; -- Holger Jakobs, Bergisch Gladbach, Tel. +49-178-9759012