RI or not RI?

Moderator: NorbertKrupa

Post Reply
Ivy
Newbie
Newbie
Posts: 14
Joined: Thu Jan 26, 2012 9:43 pm

RI or not RI?

Post by Ivy » Tue Jun 04, 2013 2:22 am

Hi,

I am building my first Vertica schema and am wondering if I should add foreign key constraints (RI) in the tables. Coming from an OTLP background, I am a big proponent of RI. How well does Vertica work RI and are there any pros and cons? Can I turn it on and off during data loading time? Any ideas are greatly appreciated!

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: RI or not RI?

Post by nnani » Tue Jun 04, 2013 3:28 pm

Hello Peng,

Unfortunately you cannot enforce Primary key constraint or any constraint while loading data into Vertica.
Vertica checks for Constraint violation when queries are run and not when data is loaded.
Vertica does not check for any constraints while loading data. So it is possible that you may end up with duplicate data even though you defined one of your columns as a primary key in the table.

Offcourse, there are workarounds and methods to do enforce unique data into you primary key column with Vertica.

The Copy command comes with a parameter called 'NO commit'. you can use this and load with COPY command. The transaction will not be commited, then you can check for corrupt data through the reject files, When you find the corrupted data and repair it, again run the COPY command.
And also you can use the 'ANALYZE_CONSTRAINT' function after loading the data using the no commit option.
This fuction can show you where there are constraint violation, The function can be run on database level,schema level, table level.

Hope this helped you.
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: RI or not RI?

Post by JimKnicely » Tue Jun 04, 2013 4:00 pm

There is one exception... A table's foreign key constraints are enforced during data load if there is a pre-join projection that has that table as its anchor table.
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

User avatar
nnani
Master
Master
Posts: 302
Joined: Fri Apr 13, 2012 6:28 am
Contact:

Re: RI or not RI?

Post by nnani » Tue Jun 04, 2013 4:05 pm

Yes, That right. It is the only exception :P
nnani........
Long way to go

You can check out my blogs at vertica-howto

User avatar
JimKnicely
Site Admin
Site Admin
Posts: 1825
Joined: Sat Jan 21, 2012 4:58 am
Contact:

Re: RI or not RI?

Post by JimKnicely » Tue Jun 04, 2013 10:17 pm

Peng,

When I first started using Vertica I didn't think it was important to use FK constraints. However, I've learned two important reasons why we would want to use them (other than the obvious data integrity benefit).
  • 1. In Vertica, the fact table's join columns are required to have FOREIGN KEY constraints in order to participate in pre-join projections. So if we want to take advantage of the pre-join projections and the performance gains they can provide, we'll need to create FKs.
    2. Creating FK constraints helps Vertica know which tables are the dimension tables and which are the fact tables. This helps Vertica decide on the best explain plan for your queries. If you are not segmenting your dimension tables, and Vertica can identify the dimension tables, it'll know that it can find that data locally on the server the portion of the query is executing in parallel on (based on data in your fact table, which is segmented).
I hope this helps!
Jim Knicely

Image

Note: I work for Vertica. My views, opinions, and thoughts expressed here do not represent those of my employer.

Ivy
Newbie
Newbie
Posts: 14
Joined: Thu Jan 26, 2012 9:43 pm

Re: RI or not RI?

Post by Ivy » Thu Jun 06, 2013 2:35 am

Thank you both nnani and Jim for your advise! It is helpful to understand how the copy process works and how RI affects Vertica.

NorbertKrupa
GURU
GURU
Posts: 527
Joined: Tue Oct 22, 2013 9:36 pm
Location: Chicago, IL
Contact:

Re: RI or not RI?

Post by NorbertKrupa » Thu Apr 24, 2014 2:23 am

nnani wrote:Offcourse, there are workarounds and methods to do enforce unique data into you primary key column with Vertica.
How about loading into a temp table and using MERGE?
Checkout vertica.tips for more Vertica resources.

Post Reply

Return to “New to Vertica”