[SPARK-21784] Add ALTER TABLE ADD CONSTRANT DDL to support defining primary key and foreign keys

• Type: Sub-task
• Status: In Progress
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 3.1.0
• Component/s: SQL
• Assignee: Unassigned
• Reporter: Suresh Thalamati
• Created: 18/Aug/17 08:29
• Updated: 15/Jul/21 12:03

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;

Issue Links

• blocks: SPARK-21823 ALTER TABLE table statements such as RENAME and CHANGE columns should raise error if there are any dependent constraints.
• blocks: SPARK-21824 DROP TABLE should automatically drop any dependent referential constraints or raise error.
• links to: [Github] Pull Request #18994 (sureshthalamati)
• links to: GitHub Pull Request #18994