Do tables in Vertica has primary and secondary keys

Question

Do projections in vertica have primary keys, secondary keys? How can I find out what is the key of a projection?

Answer 1

You had best go into Vertica's docu on projections:

https://www.vertica.com/docs/10.0.x/HTML/Content/Authoring/SQLReferenceManual/Statements/CREATEPROJECTION.htm

Primary and foreign keys exist, as do unique constraints - as constraints; but these constraints are usually disabled - because they slow down a load / insert process considerably.

Even if you choose to not specify segmentation and ordering clause of a projection: each projection is either unsegmented or segmented by a value that depends from the contents of one or more non-nullable columns (usually a HASH() on one or more columns), and ORDERed by one or more columns. The ORDER BY clause in a projection definition constitutes the data access path used in that projection. It can be somehow compared to indexing in classical databases.

To find out what the access path of a projection is - the quickest way is to fire a SELECT EXPORT_OBJECTS('','<tablename>', FALSE) at it. In our previously used example, you see that it's ordered by all its four columns, and segmented by the HASH() of all its four columns, as we created the table with no primary or foreign key:

$ vsql -Atc "SELECT EXPORT_OBJECTS('','example',FALSE)"


CREATE TABLE dbadmin.example
(
    fname varchar(4),
    lname varchar(5),
    hdate date,
    salary numeric(7,2)
);


CREATE PROJECTION dbadmin.example_super /*+basename(example),createtype(L)*/ 
(
 fname,
 lname,
 hdate,
 salary
)
AS
 SELECT example.fname,
        example.lname,
        example.hdate,
        example.salary
 FROM dbadmin.example
 ORDER BY example.fname,
          example.lname,
          example.hdate,
          example.salary
SEGMENTED BY hash(example.fname, example.lname, example.hdate, example.salary) ALL NODES OFFSET 0;