Hi all, Thanks Dawid for bringing this up, this is very important property of SQL and I'm big +1 to have this.
But before the discussion going deeply, I want to first mention that according to SQL standard, any unique key constraint which including primary key, should be always be enforced. Please see: 4.18.3.1 Introduction to table constraints "NOTE 50 — A unique constraint is always enforced. A table check constraint or a referential constraint can either be enforced or not enforced." Best, Kurt On Wed, Nov 20, 2019 at 5:43 PM Dawid Wysakowicz <[email protected]> wrote: > Hey, > > After an offline discussion I updated the FLIP slightly. In the initial > FLIP I put the OUT_OF_LINE definition at a wrong position. I moved it > into the statement paranthesis. I also moved the primary key to the > TableSchema class. > > I also extended sections about UNIQUE key support and how do the primary > keys affect sources and sinks. > > Please have another look. If there are no further concerns, I would like > to start the vote soon. > > Best, > > Dawid > > On 20/11/2019 04:32, Jark Wu wrote: > > Hi Dawid, > > > > Thanks for driving this. Primary key is a very important and useful > feature > > to Flink Table API / SQL. > > > > I'm +1 to the general design. > > > > And have some thoughts as following: > > > > 1. > The design says Flink only support NOT ENFORCED mode. But the DDL > > and KeyConstraint#primaryKey(..) can pass in ENFORCED mode. > > What will we do when user specify an ENFORCED mode? Shall we forbid > > it? > > > > 2. > "When creating a table, creating a primary key constraint will alter > > the columns nullability." > > I think we should force the columns to be declared NOT NULL, instead > of > > change the columns nullability implicitly. > > Otherwise, it may be confused what is the nullbility of the primary > key > > columns. > > > > 3. > "Support for Unique key is not part of the FLIP. It is just > mentioned > > to show how can we extend the primary key concept with more constraints > in > > the future." > > I think we can include Unique Key as part of the FLIP, as it is > already > > stated clearly. We can put the implemenation task of unique key in a > lower > > priority. > > > > 4. > Method for retrieving primary key constraint is in CatalogBaseTable. > > In SQL standard, primary key is declared in line or out of line. If > it > > is out of line, it is still in the schema part, i.e. besides column > > definitions and in the parentheses. > > So I think maybe primary key should belong to `TableSchema` becuase > it > > is a part of schema. > > > > CREATE TABLE xxx ( > > a int, > > b varchar, > > c bigint, > > primary key (a, b) > > ) with (...) > > > > > > Best, > > Jark > > > > > > On Tue, 19 Nov 2019 at 23:18, Dawid Wysakowicz <[email protected]> > > wrote: > > > >> Hi, > >> > >> I wanted to bring up the topic of primary key constraints that we could > >> leverage for runtime optimizations. Please have a look at the proposal > >> and I would especially draw your attention to the topic of nullability > >> of columns that are part of a primary key. Some time in the future we > >> will also be able to leverage it for upserting streaming tables. > >> > >> Here is the proposal: > >> > >> > https://cwiki.apache.org/confluence/display/FLINK/FLIP+87%3A+Primary+key+constraints+in+Table+API > >> > >> Best, > >> > >> Dawid > >> > >> > >> > >
