ducklake_table.table_id as PRIMARY KEY?

[llersch]

In the definition of table ducklake_schema we have:

CREATE TABLE ducklake_schema (schema_id BIGINT PRIMARY KEY, schema_uuid UUID, begin_snapshot BIGINT, end_snapshot BIGINT, schema_name VARCHAR);

The schema_id column is a primary key, and according to the documentation it is assigned from

• schema_id is the numeric identifier of the schema. schema_id is incremented from next_catalog_id in the ducklake_snapshot table.

A similar situation occurs in ducklake_table table:

• table_id is the numeric identifier of the table. table_id is incremented from next_catalog_id in the ducklake_snapshot table.

And the definition is:

CREATE TABLE ducklake_table (table_id BIGINT, table_uuid UUID, begin_snapshot BIGINT, end_snapshot BIGINT, schema_id BIGINT, table_name VARCHAR);

Shouldn't table_id be declared as primary key as well. Why not?

[Mytherin]

table_id is not declared as primary key because the same table can exist multiple times in case of renames. Actually thinking about this - schema_id should not be a primary key either - it is only not a problem currently because we don't yet support schema renames.

[llersch]

Thanks, it makes sense. So, if I were to rename a table, I would have to set the end_snapshot = <current_snapshot> of the previous record in ducklake_table for that table_id and add a new record with the same table_id and set start_snapshot = <current_snapshot>?

If we have to add a new record to ducklake_table anyway, what is the advantage of re-using the same table_id? It could be seen as a different table from DuckLake point of view.

[llersch]

Nevermind. I see that if we re-use the same table_id then it means we don't have to go over all the other DuckLake tables to update the references to it. You can close this issue.

Thanks!

[pepijnve]

I understand the rationale given above for not declaring table_id as primary key, but since it (and other ids) is used as foreign key reference in other tables this introduces an ambiguity risk in the data model. Simply put, you can have multiple conflicting rows with overlapping snapshot ranges.

Is the intention to solve this at the application layer? Since duck lake is intended to be a specification, it might be useful to add the uniqueness and referential integrity constraints for each table somewhere. You can kind of puzzle it together already, but a specification should not leave this as an exercise for the reader imo.