I have created a table in CRDB without having any PRIMARY KEY and populated my data into the table.
Now that the table is created and populated with data I want to apply a constraint to make the first column as PRIMARY KEY. But I get an error:
The constraint is:
ALTER TABLE “MyDB”.“SALES” ADD CONSTRAINT “SALES_PK” PRIMARY KEY (“S_ID”);
and the error is:
pq: multiple primary keys for table “SALES” are not allowed.
I think there reason is in CRDB if no PRIMARY KEY is mentioned, CRDB automatically will assign a PRIMARY KEY to the table named: rowid.
How can I see PRIMARY KEY in a table in CRDB? and How can I Make the column I want as PRIMARY KEY?
Currently, CockroachDB has the limitation that you cannot modify the primary key of a table after it is created. If you create a table without a primary key, under the hood an invisible rowid column will be created for you and set as the primary key.
You will have to recreate your table with a primary key set in the CREATE TABLE expression.