Integrity Constraints in Apache Hive

Since Hive 2.1.0, non-validated primary and foreign key constraints have been supported. When constraints are provided, several SQL tools generate more efficient queries. Since these constraints are not checked, data integrity must be ensured before it is put into Hive by an upstream system.

Refer to the below examples from the official documentation:

CREATE TABLE emp(
empid INT,
empno INT,
primary key(empID, empno)
disable novalidate);

CREATE TABLE empSal(
empid INT,
empno INT,
sal DECIMAL(9,2),
paidmonth INT,
CONSTRAINT c1 foreign key(empid, empno)
REFERENCES emp(empid, empno) disable novalidate);

CREATE TABLE sometable(
SomeID INT UNIQUE disable novalidate,
SomeID2 INT NOT NULL,
StrCol String DEFAULT current_user(),
price double CHECK (price > 0 AND price <= 1000));

CREATE TABLE someTable(
id1 INT,
id2 INT,
constraint c1_unique UNIQUE(id1)
disable novalidate);

CREATE TABLE someTable(
id1 INT,
id2 INT,
CONSTRAINT c1_check CHECK(id1 + id2 > 0));

I hope you found this post to be both informative and useful.

Please continue to follow us for more interesting information.