Explains what the feature is or what its benefits are to the user or customer.
Use this procedure to change the primary key for a table. But use it with caution, particularly if you are changing to a primary key for which values are not unique.
You can change the primary key of a table without having to TRUNCATE it first and reload the data. However, changing the primary key could result in data deletion. This is because of the upsert behavior which is applied when multiple rows have the same primary key. This is very important to understand ahead of time, if you are considering changing to a primary key for which values are not unique.
To change the primary key, first remove any existing primary key, and then define a new one (if any). You do not need to truncate the tables to do this operation beginning in version 3.2. Any dependent objects (pinboards or worksheets) will remain intact.
To change the primary key of a table:
- Take a snapshot.
- Connect to the database with the ThoughtSpot SQL Command Line (TQL).
Drop the existing primary key (if any), by issuing a command like this
TQL> ALTER TABLE "cart" DROP CONSTRAINT PRIMARY KEY;
Add a new primary key, if desired:
TQL> ALTER TABLE "cart" ADD CONSTRAINT PRIMARY KEY ("owner_id");
- Test that any dependent objects (pinboards, worksheets, etc.) are still working correctly.
Delete the snapshot you created earlier using the command:
tscli snapshot delete <name>