PK/FK relation question

Apparently this is Vertica's behaviour: A foreign key can only reference the primary key column(s) of the table it is referencing to. It's absolutely consistent with the standard, though, and it encourages a clean design.

And in my (not so) humble opinion, we should use extreme parsimony with constraints and other things slowing down processes in Big Data scenarios anyway ...

This is the test I ran - the error I caused can be seen in the comments:

DROP TABLE IF EXISTS parent;
CREATE TABLE parent (
  p_key     INT  NOT NULL 
, p_id      INT  NOT NULL
, p_from_dt DATE NOT NULL
, p_name    VARCHAR(32)
, CONSTRAINT pk_parent PRIMARY KEY(p_key)
, CONSTRAINT uq_parent UNIQUE(p_id,p_from_dt)
);

DROP TABLE IF EXISTS child;
CREATE TABLE child (
  c_key     INT  NOT NULL
, c_id      INT  NOT NULL
, c_from_dt DATE NOT NULL
, p_key     INT  NOT NULL
, p_id      INT  NOT NULL
, c_name    VARCHAR(32)
, CONSTRAINT pk_child PRIMARY KEY(c_key)
, CONSTRAINT uq_child UNIQUE(c_id,c_from_dt)
, CONSTRAINT fk_c2p_pk FOREIGN KEY(p_key) REFERENCES parent
-- the below constraint would lead to an error:
-- "ERROR 4550:  Referenced primary key constraint
--    does not exist"
-- , CONSTRAINT fk_c2p_uq FOREIGN KEY(p_id,c_from_dt) 
--       REFERENCES parent(p_id,p_from_dt)
);

Hi, I got more clarification from the customer.
Apparently T-SQL (MS SQL Server, Sybase) allow multiple PK on a table's unique indexes. This is not standard and not supported by PL-SQL (Oracle, Postgres, us)
They can live without this. What they really wanted was a graphical view or catalog management tool comparable to SSMS. I've suggested Squirrel SQL as a freeware tool that offers graph views of the schema.

Well - the standard is after Codd and ANSI, as far as I'm concerned.

According to those standards, as to primary key, it's like in Highlander: "there can only be one".

And, yes, you can have as many unique constraints as you like. Vertica keeps them as constraints, row oriented databases keep them as Unique Indexes.

I would also encourage naming conventions that can support meta data grinders. They usually go along the lines of having a , say, 3-letter short code/acronym/abbrev for each table, so that the Customer Dimension, for example, would look like this:

CREATE TABLE d_cust_scd (
  cust_sk   INT NOT NULL DEFAULT HASH(cust_id,cust_from_dt)
, cust_id    INT NOT NULL
, cust_from_dt DATE NOT NULL
, cust_to_dt DATE NOT NULL
, cust_is_current BOOLEAN NOT NULL
, cust_chg_ts TIMESTAMP
[...]
);

The fact table would also contain the column cust_sk as its foreign key to the customer dimension, so that we can also use the USING() clause for the join, for example. Many meta data grinders take advantage of that, too ...