Uploaded image for project: 'Hive'
  1. Hive
  2. HIVE-16575

Support for 'UNIQUE' and 'NOT NULL' constraints

    XMLWordPrintableJSON

Details

    Description

      Follow-up on HIVE-13076.

      This issue add support for SQL 'UNIQUE' and 'NOT NULL' constraints when we create a table / alter a table (https://www.postgresql.org/docs/9.6/static/sql-createtable.html).

      As with PK and FK constraints, currently we do not enforce them; thus, the constraints need to use the DISABLE option, but they will be stored and can be enabled for rewriting/optimization using RELY.

      This patch also adds support for inlining the constraints next to the column type definition, i.e., 'column constraints'.

      Some examples of the extension to the syntax included in the patch:

      CREATE TABLE table3 (x string NOT NULL DISABLE, PRIMARY KEY (x) DISABLE, CONSTRAINT fk1 FOREIGN KEY (x) REFERENCES table2(a) DISABLE); 
CREATE TABLE table4 (x string CONSTRAINT nn4_1 NOT NULL DISABLE, y string CONSTRAINT nn4_2 NOT NULL DISABLE, UNIQUE (x) DISABLE, CONSTRAINT fk2 FOREIGN KEY (x) REFERENCES table2(a) DISABLE, 
CONSTRAINT fk3 FOREIGN KEY (y) REFERENCES table2(a) DISABLE);
CREATE TABLE table12 (a STRING CONSTRAINT nn12_1 NOT NULL DISABLE NORELY, b STRING);
CREATE TABLE table13 (a STRING NOT NULL DISABLE RELY, b STRING);
CREATE TABLE table14 (a STRING CONSTRAINT nn14_1 NOT NULL DISABLE RELY, b STRING);
CREATE TABLE table15 (a STRING REFERENCES table4(x) DISABLE, b STRING);
CREATE TABLE table16 (a STRING CONSTRAINT nn16_1 REFERENCES table4(x) DISABLE RELY, b STRING);
ALTER TABLE table16 CHANGE a a STRING REFERENCES table4(x) DISABLE NOVALIDATE;
ALTER TABLE table12 CHANGE COLUMN b b STRING CONSTRAINT nn12_2 NOT NULL DISABLE NOVALIDATE;
ALTER TABLE table13 CHANGE b b STRING NOT NULL DISABLE NOVALIDATE;

      Attachments

        1. HIVE-16575.patch
          5.27 MB
          jcamachorodriguez
        2. HIVE-16575.04.patch
          5.38 MB
          jcamachorodriguez
        3. HIVE-16575.03.patch
          5.33 MB
          jcamachorodriguez
        4. HIVE-16575.02.patch
          5.29 MB
          jcamachorodriguez
        5. HIVE-16575.01.patch
          5.29 MB
          jcamachorodriguez

        Issue Links

          is related to

          Bug - A problem which impairs or prevents the functions of the product. HIVE-20843 RELY constraints on primary keys and foreign keys are not recognized

          • Major - Major loss of function.
          • Resolved

          Bug - A problem which impairs or prevents the functions of the product. HIVE-16603 Enforce foreign keys to refer to primary keys or unique keys

          • Major - Major loss of function.
          • Closed

          New Feature - A new feature of the product, which has yet to be developed. HIVE-6905 Implement Auto increment, primary-foreign Key, not null constraints and default value in Hive Table columns

          • Major - Major loss of function.
          • Open

          New Feature - A new feature of the product, which has yet to be developed. HIVE-16605 Enforce NOT NULL constraints

          • Major - Major loss of function.
          • Closed

          Improvement - An improvement or enhancement to an existing feature or task. HIVE-16604 Use [NOT] ENFORCED for column constraint characteristics

          • Major - Major loss of function.
          • Closed
          relates to

          Improvement - An improvement or enhancement to an existing feature or task. HIVE-13076 Implement FK/PK "rely novalidate" constraints for better CBO

          • Major - Major loss of function.
          • Resolved
          links to

          Web Link GitHub Pull Request #175

          Activity

            People

              jcamacho Jesús Camacho Rodríguez
              jcamacho Jesús Camacho Rodríguez
              Votes:
              0 Vote for this issue
              Watchers:
              7 Start watching this issue

              Dates

                Created:
                Updated:
                Resolved: