Add Primary Key to Existing Table

How to add Primary Key to an existing table?

Recommended solution - remove and re-create as primary key:

ALTER TABLE teachers 
    DROP COLUMN id,
    ADD COLUMN id INT NOT NULL AUTO_INCREMENT PRIMARY KEY;

https://dbfiddle.uk/?rdbms=mysql_8.0&fiddle=26c8d719ff1e3a77297962029a587b65

Adding a primary key column to an existing table in SQL Server?

Simply alter the table and add the column with the appropriate characteristics.

alter table x add id smallint identity(1,1) not null primary key; 

Thrown together quickly and you probably should get in the habit of naming constraints. fiddle. I will note that you may or may not want to use an identity column - think carefully about your actual goal and how you want to continue using this table after the alteration.

How to add a primary key and foreign key to an existing table in Microsoft SQL Server database

Could you try this?

ALTER TABLE table123
ALTER COLUMN customerid INT NOT NULL

Then run :

ALTER TABLE table123
ADD PRIMARY KEY (customerid)

Then,

ALTER TABLE items_ordered
ADD FOREIGN KEY (customerid)
REFERENCES table123 (customerid)

You need to change your column definition to not null, and make sure all values are unique as well

Add PRIMARY KEY constraint to existing table

Use ALTER TABLE:

ALTER TABLE user_user_user_permissions
ADD CONSTRAINT user_user_user_permissions_id_pkey PRIMARY KEY (id);

Of course, data in the id column must be UNIQUE and NOT NULL, or you get an exception trying.

If a different PRIMARY KEY constraint exists, drop that first:

ALTER TABLE user_user_user_permissions
  DROP CONSTRAINT old_constraint_name
, ADD CONSTRAINT user_user_user_permissions_id_pkey PRIMARY KEY (id);

You can look up the name of the existing constraint in pgAdmin under "Constraints" like you show in the picture.

Add a primary key column to an old table

(From 12.1) You can add a new auto-incremented surrogate key to a table with either:

alter table t
  add ( t_id integer generated by default as identity );

Or

create sequence s;
alter table t
  add ( t_id integer default s.nextval );

These set the value for all the existing rows. So may take a while on large tables!

You should also look to add a unique constraint on the business keys too though. To do that, take the steps Marmite Bomber suggests.

How to add a primary key to a MySQL table?

After adding the column, you can always add the primary key:

ALTER TABLE goods ADD PRIMARY KEY(id)

As to why your script wasn't working, you need to specify PRIMARY KEY, not just the word PRIMARY:

alter table goods add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;

---

Related Topics