Uploaded image for project: 'Spark'
  1. Spark
  2. SPARK-19842 Informational Referential Integrity Constraints Support in Spark
  3. SPARK-21784

Add ALTER TABLE ADD CONSTRANT DDL to support defining primary key and foreign keys

    XMLWordPrintableJSON

    Details

    • Type: Sub-task
    • Status: In Progress
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 3.0.0
    • Fix Version/s: None
    • Component/s: SQL
    • Labels:
      None

      Description

      Currently Spark SQL does not have DDL support to define primary key , and foreign key constraints. This Jira is to add DDL support to define primary key and foreign key informational constraint using ALTER TABLE syntax. These constraints will be used in query optimization and you can find more details about this in the spec in SPARK-19842

      Syntax :

      ALTER TABLE [db_name.]table_name ADD [CONSTRAINT constraintName]
        (PRIMARY KEY (col_names) |
        FOREIGN KEY (col_names) REFERENCES [db_name.]table_name [(col_names)])
        [VALIDATE | NOVALIDATE] [RELY | NORELY]
      

      Examples :

      ALTER TABLE employee _ADD CONSTRANT pk_ PRIMARY KEY(empno) VALIDATE RELY
      ALTER TABLE department _ADD CONSTRAINT emp_fk_ FOREIGN KEY (mgrno) REFERENCES employee(empno) NOVALIDATE NORELY
      

      Constraint name generated by the system:

      ALTER TABLE department ADD PRIMARY KEY(deptno) VALIDATE RELY
      ALTER TABLE employee ADD FOREIGN KEY (workdept) REFERENCES department(deptno) VALIDATE RELY;
      

        Attachments

          Issue Links

            Activity

              People

              • Assignee:
                Unassigned
                Reporter:
                tsuresh Suresh Thalamati
              • Votes:
                0 Vote for this issue
                Watchers:
                6 Start watching this issue

                Dates

                • Created:
                  Updated: