ALTER TABLE support for adding constraints

[Mytherin]

Constraints should be able to be dropped/created separately from the CREATE TABLE statement (i.e. as separate ALTER TABLE ADD CONSTRAINT statements). Constraints also need names for this.

[taniabogatsch]

For example support the following SQL statements.

• ALTER TABLE tbl ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n);
• ALTER TABLE tbl ADD PRIMARY KEY (id);
• ALTER TABLE tbl DROP CONSTRAINT constraint_name;
• ALTER TABLE tbl RENAME CONSTRAINT constraint_name TO new_constraint_name;

[papparapa]

I'm interested in working on this.
At first, constraints have to be named.
Is it a good way to implement ConstraintCatalogEntry that inherits StandardEntry?
Inheriting StandardEntry should lead to constraints having name and also oid.

[taniabogatsch]

Hi @papparapa. Nice to hear that you are interested in working on this. Yes, that makes sense; we could add constraints to the catalog like we currently add indexes (duck_index_entry.hpp/index_catalog_entry.hpp).

Since all these constraints create an index in the background, I'd suggest reusing the existing code for the CREATE UNIQUE INDEX idx_name ON table (expression) statement as much as possible for this issue. Make sure to add the constraints to both the table catalog entry and its storage; have a look at PhysicalCreateIndex::Finalize (physical_create_index.hpp).

And let me know if you have more specific questions or anything. :)

[papparapa]

Thanks @taniabogatsch for some hints!
I will read relevant codes.

[coffepowered]

Hello @papparapa , I am interested in this functionality as well. Did you make any progress?

[papparapa]

@coffepowered No progress yet, so feel free to work on it.

[lnkuiper]

@coffepowered Have you made any progress on this? If not, I would like to assign this task:

ALTER TABLE tbl RENAME CONSTRAINT constraint_name TO new_constraint_name;

to @kriti-sc

Edit: this is assigned to @kriti-sc until further notice.

[chienguo]

Hi @taniabogatsch. There exists a TableCatalogEntry that already includes constraints, so do we need another ConstraintCatalogEntry to support this alter table operation, instead of just modifying the constraint in that table entry?

[github-actions]

This issue is stale because it has been open 90 days with no activity. Remove stale label or comment or this will be closed in 30 days.

[loicalleyne]

Is adding this feature still on the roadmap? I'm currently having to implement an table schema auto-patcher workflow for when a data source's schema evolves and new columns need to be added and this is complicating things for tables that need a primary key. The ability to specify a primary key in a CREATE TABLE t1 AS SELECT * FROM read_json(...) || read_parquet(...) would be a helpful alternative solution.

[taniabogatsch]

@loicalleyne, on the roadmap, yes. But I can't say when we'll have this. Afaik, there have been two people working on it, but we never got a PR.

[Ammar-Azman]

Still not supported?

[frapa]

Hi, I would like to give this a go. I know there were a couple of people that previously tried implementing this, but it's almost a year that there is no activity, so I believe I wouldn't be stepping on anybody's toes. @lnkuiper @kriti-sc please shout if you are still on it!

My plan would be to start with

ALTER TABLE table ADD PRIMARY KEY (column);

because this can be implemented without the constraint naming capabilities. It would then be possible to add a primary key but not remove it.

If I understood correctly, adding a PRIMARY KEY is the same as adding a NOT NULL and UNIQUE constraint. I didn't figure out yet if I need to explicitly create the ART index for the unique constraint or if adding the constraints in DuckTableEntry is sufficient.

Let me know if you find this plan reasonable.

I also have some additional questions on the expected API:

• If one adds a primary key with ALTER TABLE table ADD PRIMARY KEY (column);, do we intend to support deleting this constraint? If so, will this constraint be assigned an automatic name? I think postgres does, using the column name suffixed with _pkey.
• Do we intend to also support adding and removing FOREIGN KEYS as shown in the postgres examples here?