Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Create Primary Key as a part of the main table definition instead of a separate ALTER TABLE command #1285

Closed
mrajcevic01 opened this issue Jan 22, 2024 · 1 comment
Labels
triage Needs to be triaged

Comments

@mrajcevic01
Copy link

mrajcevic01 commented Jan 22, 2024

Today when we do a schema export with Voyager create the table using CREATE TABLE and then separately create the primary key using ALTER TABLE...SET PRIMARY KEY. I have seen two issues with this method:

  1. Causes longer import times (creating the table with the primary key is faster than creating the table and then later changing the primary key)
  2. We get an error when trying to migrate a table that needs to be partitioned on the target. This is a process specific to taking an existing application and making changes for it be geo-partitioned. (meaning it was not partitioned on the source but instead we edit the SQL file after the export so that it will be partitioned on the target). The error below:

x_restore_multi_region=# ALTER TABLE ONLY public.account_values ADD CONSTRAINT account_values_pkey PRIMARY KEY (id); ERROR: insufficient columns in PRIMARY KEY constraint definition DETAIL: PRIMARY KEY constraint on table "account_values" lacks column "geo_location" which is part of the partition key. x_restore_multi_region=# ALTER TABLE ONLY public.account_values ADD CONSTRAINT account_values_pkey PRIMARY KEY (id,geo_location); ERROR: changing primary key of a partitioned table is not yet implemented HINT: See https://github.com/yugabyte/yugabyte-db/issues/16980. React with thumbs up to raise its priority

There is a YSQL GH issue already open to support this issue (#16980) .

We can avoid both of this by creating the PRIMARY KEY as part of the CREATE TABLE syntax as shown in our examples.

@mrajcevic01
Copy link
Author

Closing this as its a duplicate of #612

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
triage Needs to be triaged
Projects
None yet
Development

No branches or pull requests

1 participant