Alter Table Primary key - Crate DB

Question

I want to alter a table in my Crate DB to change the primary key constraint to add a column to the existing one. If I need to drop the constraint and create a new one what would be the SQL syntax for the same. I have been trying the conventional SQL syntax and it does not seem to work:

alter table my_data_table drop primary key;

the above command gives an error:

SQLActionException[SQLParseException: line 1:34: no viable alternative at input 'alter table my_data_table drop']

I checked the Alter table SQL reference and can only find ways to add columns but nothing about altering the constraints.So if you are aware of how to do this, please let me know.
cheers!

what database are you using? – sam100rav Jul 23 '17 at 12:28 — sam100rav, Jul 23 '17 at 12:28
its a Crate DB http://crate.io – Nachiketh Jul 23 '17 at 12:29 — Nachiketh, Jul 23 '17 at 12:29

score 1 · Accepted Answer · answered Jul 24 '17 at 11:21

there's no way to alter the primary key once a table has been created. You need to create a new table that has the schema you'd like to have and then either move the data over with COPY TO and COPY FROM or with insert into to_table (i) (select ... from t). With CrateDB > 2.0 it's also possible to rename tables, so you can still use the original table name.

score -1 · Answer 2 · answered Jul 23 '17 at 12:31

-1

First use the following code snippets for finding the constraints

SELECT Col.Column_Name from 
    INFORMATION_SCHEMA.TABLE_CONSTRAINTS Tab, 
    INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE Col WHERE 
       Col.Constraint_Name = Tab.Constraint_Name
       AND Col.Table_Name = Tab.Table_Name
       AND Constraint_Type = 'PRIMARY KEY'
       AND Col.Table_Name = '<your table name>'

Then use this to drop the constraint

ALTER TABLE Customer DROP CONSTRAINT Constraint_Name;

P.S: considering you are using SQL SERVER

answered Jul 23 '17 at 12:31

Md. Saifur Rahman

67
7

thank you, but I'm using a Crate DB and not SQL Server, this wont work on it. – Nachiketh Jul 23 '17 at 12:34
From [documentaion](https://crate.io/docs/crate/reference/sql/reference/constraints.html), "Adding a PRIMARY KEY column is only possible if the table is empty." If your table doesn't contain any data yet, you can easily update your table creation command. If the table has content, then sorry you cannot change primary key (but can add another column ([using alter table](https://crate.io/docs/crate/reference/sql/reference/alter_table.html))). – Md. Saifur Rahman Jul 23 '17 at 12:47

Alter Table Primary key - Crate DB

2 Answers2

Linked

Related