Menu
I have a table in postgresql with a composite primary key. The primary key consists of two columns named:
DATETIME, UID
I have a another (non-null) column named ACTION already existing in this table. How do I add ACTION to the composite primary key? Ie: I'd like the resulting primary key of the table to be the triplet:
DATETIME, UID, ACTION
875
As the PK moves, the data must be shuffled around disk to keep it in the correct order. Depending on the data set MySQL may have to move many physical rows to do this. The same goes for updating your PK. Changing the PK changes the order on disk and requires many rows to be moved.
ALTER TABLE PrimaryKeyColumnAdd ALTER COLUMN Column2 int NOT NULL ; And step two update the primary key. Don't forget that the primary key is a constraint and constraints cannot be altered only dropped and re-created. PRIMARY KEY (Column1, Column2);
Oracle creates an index on the columns of a primary key; therefore, a composite primary key can contain a maximum of 16 columns. To define a composite primary key, you must use the table_constraint syntax rather than the column_constraint syntax.
First drop the primary key constraint. You can get the name of the constraint by typing
\d my_table
and look under indexes for something like:
"my_table_pkey" PRIMARY KEY, btree (datetime, uid)
Drop it by doing:
alter table my_table drop constraint my_table_pkey;
Then create the new composite primary key by doing:
alter table my_table add constraint my_table_pkey primary key (datetime, uid, action);
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
