Constraints

[mverrilli]

"The primary, foreign and index constraints return nothing. This is because I'm told these are 'enforced on select'. Maybe a Vertica expert can tell me the correct way to handle this."

Primary, foreign and unique constraints are not checked during normal insert operations. They might be caught on select (in the form of an error) if you have broken constraints and do a query that assumes these constraints are valid in the data. There is a method for checking constraints after a load using ANALYZE_CONSTRAINTS(). This is likely best left handled manually.

You can, however, assume that if these constraints exist that they can be used for ORM generation purposes, though.

My feeling is that people using ORM inserts with Vertica are either logging or trickling data in and should be sure of their keys before insert (or stage and check). Vertica is not an OLTP database, so many of those style ORM usage should be rare.

I am new to SQLAlchemy so I don't know all the ins and outs, but for the type of database Vertica is, generally I would expect to see larger selects, not by key row retrieval.

Some last quick advice regarding these constraints... you cannot assume that if there is a PK or AK defined, that querying on it will perform well. That type of data access will be more about what columns you are pulling and which sort order the projections have which can materialize those columns. There are no indexes in Vertica, and this includes PK and AK.

Finally, because there wasn't a good segue to it, NOT NULL constraints are checked on insert