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

    • Sub-task
    • Status: In Progress
    • Major
    • Resolution: Unresolved
    • 3.1.0
    • None
    • SQL
    • 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

              Unassigned Unassigned
              tsuresh Suresh Thalamati
              Votes:
              2 Vote for this issue
              Watchers:
              13 Start watching this issue

              Dates

                Created:
                Updated: