[jira] [Resolved] (HIVE-26324) Add "one-row-table" constraints on NOTIFICATION_SEQUENCE table

["Sourabh Badhya (Jira)" <ji...@apache.org>]

     [ https://issues.apache.org/jira/browse/HIVE-26324?page=com.atlassian.jira.plugin.system.issuetabpanels:all-tabpanel ]

Sourabh Badhya resolved HIVE-26324.
-----------------------------------
    Target Version/s: 4.0.0-alpha-2
          Resolution: Fixed

> Add "one-row-table" constraints on NOTIFICATION_SEQUENCE table
> --------------------------------------------------------------
>
>                 Key: HIVE-26324
>                 URL: https://issues.apache.org/jira/browse/HIVE-26324
>             Project: Hive
>          Issue Type: Task
>            Reporter: Sourabh Badhya
>            Assignee: Sourabh Badhya
>            Priority: Minor
>              Labels: pull-request-available
>          Time Spent: 40m
>  Remaining Estimate: 0h
>
> NOTIFICATION_SEQUENCE table must have only one row however there have been several reports of NOTIFICATION_SEQUENCE table having multiple rows. In order to prevent this situation from happening, its best to enforce "one-row-table" like constraints on NOTIFICATION_SEQUENCE table.
> Queries tried on supported databases - 
> NOTIFICATION_SEQUENCE already has NNI_ID as the primary key. This will help us in adding "one-row-table" like constraints.
> *MySQL* - 
> {code:java}
> ALTER TABLE `NOTIFICATION_SEQUENCE` MODIFY COLUMN `NNI_ID` BIGINT(20) GENERATED ALWAYS AS (1) STORED NOT NULL;{code}
> CHECK constraints are not effective in MySQL 5.7. Hence need to shift to using GENERATED columns. This is supported in MySQL 5.7.
> Similarly for MariaDB which uses the same schema script as that of MySQL, Generated columns with syntax compatible with MySQL are supported from 10.2.
> Link - [https://dev.mysql.com/doc/refman/5.7/en/alter-table-generated-columns.html]
> Link - [https://mariadb.com/kb/en/generated-columns/]
> *Postgres* - 
> Either change the definition of table like this - 
> {code:java}
> CREATE TABLE "NOTIFICATION_SEQUENCE"
> (
>     "NNI_ID" BIGINT NOT NULL CHECK ("NNI_ID" = 1),
>     "NEXT_EVENT_ID" BIGINT NOT NULL,
>     PRIMARY KEY ("NNI_ID")
> ); {code}
> OR add explicit constraints like this -
> {code:java}
> ALTER TABLE "NOTIFICATION_SEQUENCE"
>     ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK ("NNI_ID" = 1); {code}
> *Derby* - 
> {code:java}
> ALTER TABLE "APP"."NOTIFICATION_SEQUENCE" ADD CONSTRAINT "ONE_ROW_CONSTRAINT" CHECK (NNI_ID = 1); {code}
> *Oracle* - 
> Either change the definition of table like this - 
> {code:java}
> CREATE TABLE NOTIFICATION_SEQUENCE
> (
>     NNI_ID NUMBER NOT NULL CHECK (NNI_ID = 1),
>     NEXT_EVENT_ID NUMBER NOT NULL
> ); {code}
> OR add explicit constraints like this -
> {code:java}
> ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK (NNI_ID = 1); {code}
> *Microsoft SQL Server* - 
> {code:java}
> ALTER TABLE NOTIFICATION_SEQUENCE ADD CONSTRAINT ONE_ROW_CONSTRAINT CHECK (NNI_ID = 1); {code}



--
This message was sent by Atlassian Jira
(v8.20.7#820007)