This topic describes simple primary key operations in Oracle mode of OceanBase Database, including adding, modifying, and dropping primary keys.
Add a primary key
The syntax for adding a primary key is as follows:
ALTER TABLE table_name ADD [CONSTRAINT constraint_name] PRIMARY KEY (column_name);
where
table_namespecifies the name of the table for which the primary key is to be added.constraint_namespecifies the name of the constraint to be added.column_namespecifies the column of the table.
For more information, see ALTER TABLE.
Assuming table tbl1 already exists in the database and you want to define the c1 column in table tbl1 as the primary key. Here is an example:
obclient> ALTER TABLE tbl1 ADD PRIMARY KEY (c1);
Modify a primary key
The syntax for modifying a primary key is as follows:
ALTER TABLE table_name MODIFY PRIMARY KEY (column_name);
Here, table_name specifies the name of the table where the primary key is to be modified, and column_name specifies the column where the primary key will be located after the modification. For more information, see ALTER TABLE.
Assuming table tbl1 already exists in the database and has an existing primary key. The example below demonstrates how to modify the primary key of table tbl1 to apply to the c2 column:
obclient> ALTER TABLE tbl1 MODIFY PRIMARY KEY (c2);
Drop a primary key
The syntax for dropping a primary key is as follows:
ALTER TABLE table_name DROP PRIMARY KEY;
Here, table_name specifies the name of the table from which the primary key is to be deleted. For more information, see ALTER TABLE.
Assuming table tbl1 already exists in the database and has an existing primary key. The example below demonstrates how to drop the primary key from table tbl1:
obclient> ALTER TABLE tbl1 DROP PRIMARY KEY;