I am new to SQL. Assume there is a table with few columns e.g.,
There are duplicates in id
column.
id | brand |
---|---|
1 | 100 |
1 | 200 |
2 | 100 |
2 | 200 |
3 | 100 |
4 | 100 |
I would like to create a new column idx
based on id and brand columns. E.g.,
id | brand | idx |
---|---|---|
1 | 100 | 101 |
1 | 200 | 201 |
2 | 100 | 102 |
2 | 200 | 202 |
3 | 100 | 103 |
4 | 100 | 104 |
So I can use idx to be my primary key in somewhere else. Any suggestions? many thanks
Exactly how you do this depends on the database, but most will be like this.
-- Add the new column.
-- It can't be the primary key yet because it will contain nulls.
alter table brands add column idx integer;
-- Set its value.
update brands set idx = id + brand;
-- Make it the primary key.
alter table brands add primary key(idx);
However, now you have to remember to set that primary key manually when you insert. You could add a default id + brand
, but it's entirely possible you will have conflicts. For example, if you have id=10, brand=190
and id=20, brand=180
both will have an idx
of 200.
If you just want a unique primary key, it's better to use an auto-incrementing primary key.
alter table brands add column idx serial primary key;
That will add the new column and populate it with a simple automatically incrementing integer. All subsequent inserts will automatically add the next number.
serial
is PostgreSQL specific, other databases will do that slightly differently such as auto_increment
in MySQL or identity
.