Primary key operations

This topic describes how to add, modify, and drop primary keys in OceanBase Database in MySQL mode.

Add a primary key

You can use the following syntax to add a primary key:

ALTER TABLE table_name ADD PRIMARY KEY (column_name);

In the syntax, table_name specifies the name of the table to which you want to add a primary key, and column_name specifies the column to which you want to add the primary key. For more information, see ALTER TABLE.

Assume that a table named tbl1 exists in the database. You can execute the following statement to add a primary key to the c1 column of the tbl1 table:

obclient> ALTER TABLE tbl1 ADD PRIMARY KEY(c1);

Modify a primary key

You can use the following syntax to modify a primary key:

ALTER TABLE table_name DROP PRIMARY KEY, ADD PRIMARY KEY (column_name);

In the syntax, table_name specifies the name of the table to which you want to add a primary key, and column_name specifies the column to which you want to add the primary key. For more information, see ALTER TABLE.

Assume that a table named tbl1 exists in the database and that a primary key exists in the tbl1 table. You can execute the following statement to modify the primary key of the tbl1 table to apply to the c2 column:

obclient> ALTER TABLE tbl1 DROP PRIMARY KEY, ADD PRIMARY KEY(c2);

Drop a primary key

You can use the following syntax to drop a primary key:

ALTER TABLE table_name DROP PRIMARY KEY;

In the syntax, table_name specifies the name of the table from which you want to drop a primary key. For more information, see ALTER TABLE.

Assume that a table named tbl1 exists in the database and that a primary key exists in the tbl1 table. You can execute the following statement to drop the primary key of the tbl1 table:

obclient> ALTER TABLE tbl1 DROP PRIMARY KEY;

References

View indexes