Using Compound Predicate Joins

Vertica allows tables to have compound (multiple-column) primary and foreign keys. For example:

-- Create a pair of tables with multi-column keys

CREATE TABLE dimension

(pk1 INTEGER NOT NULL,

pk2 INTEGER NOT NULL);

ALTER TABLE dimension

ADD PRIMARY KEY (pk1, pk2);

CREATE TABLE fact

(fk1 INTEGER NOT NULL,

fk2 INTEGER NOT NULL);

ALTER TABLE fact

ADD FOREIGN KEY (fk1, fk2)

REFERENCES dimension (pk1, pk2);

To join tables using compound keys, you must connect two join predicates with a Boolean AND operator. For example:

-- Query that joins the tables

SELECT *

FROM fact, dimension

WHERE fact.fk1 = dimension.pk1

AND

fact.fk2 = dimension.pk2;