[
https://issues.apache.org/jira/browse/FLINK-24512?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=17427457#comment-17427457
]
Ingo Bürk commented on FLINK-24512:
-----------------------------------
This is a duplicate of https://issues.apache.org/jira/browse/FLINK-24050
> Allow metadata columns can also be part of primary key
> ------------------------------------------------------
>
> Key: FLINK-24512
> URL: https://issues.apache.org/jira/browse/FLINK-24512
> Project: Flink
> Issue Type: Bug
> Components: Table SQL / API
> Reporter: Jark Wu
> Priority: Major
>
> Currently, primary key can only be defined on physical columns. However, we
> encountered some cases need to define primary key on metadata columns.
> For example, there is MySQL sharding tables {{user_01}}, {{user_02}}, ...,
> {{user_99}} and they all use snowflake algorithm to generate global unique ID
> as the PK of each table. The user would like to read the sharding tables as
> one table and load into an OLAP with primary key of table name and ID. A
> Flink SQL can be following:
> {code}
> CREATE TABLE mysql_users (
> table_name STRING NOT NULL METADATA,
> id BIGINT NOT NULL,
> user_name STRING,
> address STRING,
> PRIMARY KEY (table_name, id) NOT ENFORCED
> ) WITH (
> 'connector' = 'mysql-cdc',
> 'database-name' = 'mydb',
> 'table-name' = 'user_.*',
> 'username' = 'xxx',
> 'password' = 'yyy'
> );
> {code}
> IMO, (non-virtual) metadata columns is similar to physical columns, because
> it is also some kind **physical** data in database. And our primary key
> definition is "NOT ENFORCED", which means we simply trust the unique
> constraint by users.
--
This message was sent by Atlassian Jira
(v8.3.4#803005)
