Describe the bug

When a table name changes, the entire table is dropped (if the removal policy is DESTROY). Amazon Redshift allows the table name to be altered, but the table is being dropped, even though the original table construct is still intact (the resource ID does not change).

Expected Behavior

Table names should change

Current Behavior

A new redshift table is created, with the old table being dropped.
Provided a DESTROY removal policy, the table, and it's respective data will be deleted.

Reproduction Steps

redshift.Table(
    self,
    id="ConsistentTableId",
    table_name="old_table_name",
    removal_policy=cdk.RemovalPolicy.DESTROY,
    # other attributes needed for redshift table
)

We then change the redshift table name, this will drop the redshift table, and create a new table.

redshift.Table(
    self,
    id="ConsistentTableId",
    table_name="new_table_name",
    removal_policy=cdk.RemovalPolicy.DESTROY,
    # other attributes needed for redshift table
)

Possible Solution

Persist the redshift table when table names change, given the use of the redshift data API, we could use ALTER statements to change the name of the table. Redundant tables that are no longer used in the Stack can still be "removed" by specifying a new id/removing the existing table construct in the code.

Additional Information/Context

Obviously not all developers would add a DESTROY removal policy, but this was not the point of this issue. The point of the issue is the table not persisting through a table name change, but a new table being created. I am simply using a DESTROY removal policy for the worst case scenario, as an example.

Not quite sure if this is intended behaviour. However from my personal development experience, I would say I was surprised that tables were being dropped/being replaced in the stack.

The cdk diff command shows the following changes

[~] Custom::RedshiftDatabaseQuery RedshiftClusterStack/ConsistentTableId/Resource/Resource ConsistentTableId808D9241 
 └─ [~] tableName
     └─ [~] .prefix:
         ├─ [-] old_table_name
         └─ [+] new_table_name

The above shows to myself that we're just changing the table name, not creating a new construct

CDK CLI Version

2.65.0

Framework Version

2.65.0

Node.js Version

v16.16.0

OS

Amazon Linux 2

Language

Python

Language Version

3.7.16

Other information

This might be a feature request... If the original behaviour is intended behaviour.