[
https://issues.apache.org/jira/browse/PHOENIX-940?page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel&focusedCommentId=13979245#comment-13979245
]
Eli Levine commented on PHOENIX-940:
------------------------------------
My use case involves views. I have a very simple base table, whose PK is
{{type VARCHAR, id VARCHAR}}. There are multiple views on top of it, one of
which declares a column, say {{view1_id VARCHAR}}. I want to disallow multiple
rows in that view with a duplicate {{view1_id}} value.
Since my use case involves views, one possibility is to allow views to extend
their base table's PK. That way the base table can be declared as having only
{{type VARCHAR}} as the PK. The view would then add {{view1_id VARCHAR}} to
the PK, as well as {{id VARCHAR}} as a KV column (if needed).
DDL for base table becomes:
{code}
CREATE TABLE BASE_TABLE (type VARCHAR NOT NULL PRIMARY KEY)
{code}
DDL for view1 becomes:
{code}
CREATE VIEW VIEW1 (view1_id VARCHAR NOT NULL PRIMARY KEY, id VARCHAR) AS SELECT
* FROM BASE_TABLE WHERE type = '1'
{code}
Note that {{view1_id}} is declared as {{PRIMARY KEY}}. This would cause its
full PK to be {{type, view1_id}}, where the first PK component is inherited
from its base table.
---------------------------
I think the above scenario would serve my purpose, since I require a single
column to be unique in a view. This solution would not work for regular
Phoenix tables whose uniqueness constraint requirements are more complicated,
such as needing uniqueness on two different columns independently of each
other. Not sure if there is a real business need for implementing full-blown
uniqueness constraint functionality at this point.
> Unique Constraint
> -----------------
>
> Key: PHOENIX-940
> URL: https://issues.apache.org/jira/browse/PHOENIX-940
> Project: Phoenix
> Issue Type: New Feature
> Reporter: Eli Levine
>
> Add support for UNIQUE constraint on one or more columns in CREATE and ALTER
> TABLE statements:
> {code}
> CONSTRAINT constraint_name UNIQUE (column1, column2, . column_n)
> {code}
> One possible way to implement this is by adding a secondary index on columns
> involved in the constraint and adding a check for column values before upsert.
--
This message was sent by Atlassian JIRA
(v6.2#6252)
