Loading Data into Pre-join Projections

NiravLangaliya

Hi All,

I am following link to check that how data is being loaded into Pre-Join Projection :

https://my.vertica.com/docs/7.1.x/HTML/index.htm#Authoring/AdministratorsGuide/BulkLoadCOPY/LoadingDataIntoPre-joinProjections.htm?Highlight=Loading Data into Pre-join Projections

and there is notes that:

Foreign and Primary Key Constraints

To ensure referential integrity, foreign and primary key constraints are enforced on inserts into fact tables of pre-join projections. If a fact row attempts to reference a row that does not exist in the dimension table, the load is automatically rolled back. The load is also rolled back if a fact row references more than one dimension row.

Note: Unless it also has a NOT NULL constraint, a column with a FOREIGN KEY constraint can contain a NULL value even though the dimension table's primary key column does not contain a NULL value. This allows for records to be inserted into the fact table even though the foreign key in the dimension table is not yet known.

what I take from this note is if fact table has foreign key with not null data type in that case we can able to load data even though data is not present into Dimension table but when i tried to create pre - join projection using fact table which has foreign key without not null specified it is giving me error and not allowing me to create it.

DROP TABLE IF EXISTS public.emp_table_dim_nl;

CREATE TABLE public.emp_table_dim_nl (
EmployeeID integer NOT NULL,
FirstName VARCHAR(30),
LastName VARCHAR(30),
Dpt_ID integer
);

DROP TABLE IF EXISTS public.dpt_table_dim_nl;

CREATE TABLE public.dpt_table_dim_nl (
Dpt_ID integer NOT NULL,
Dpt_Name VARCHAR(20)
)
;

Creating Primary Key:
ALTER TABLE public.emp_table_dim_nl ADD CONSTRAINT pk_emp_dimension PRIMARY KEY (EmployeeID);
ALTER TABLE public.dpt_table_dim_nl ADD CONSTRAINT pk_dpt_dimension PRIMARY KEY (Dpt_ID);

Creating Foreign Key:
ALTER TABLE public.emp_table_dim_nl ADD CONSTRAINT fk_dep_id FOREIGN KEY (Dpt_ID) REFERENCES public.dpt_table_dim_nl (Dpt_ID);

INSERT INTO public.dpt_table_dim_nl values (1,'A');
INSERT INTO public.dpt_table_dim_nl values (2,'B');

INSERT INTO public.emp_table_dim_nl values (32,'N','L',1);
INSERT INTO public.emp_table_dim_nl values (36,'Nila','K',2);

CREATE PROJECTION public.EMP_INFO
(EmployeeID, FirstName, LastName, Dpt_Name)
AS (
SELECT
EmployeeID,
FirstName,
LastName,
Dpt_Name
FROM public.emp_table_dim_nl emp,
public.dpt_table_dim_nl dpt
WHERE emp.Dpt_ID = dpt.Dpt_ID
) KSAFE 1;

CREATE - 0 row(s), 0.000 secs Error Code: 5630, SQL State: 42601
Vertica VJDBC(5630) ERROR: Nullable FKs are not allowed in projection definition
... 1 statement(s) executed, 0 row(s) affected, exec/fetch time: 0.000/0.000 sec 0 successful, 0 warnings, 1 errors