Nullable FKs are not allowed in projection definition

[malargopal]

Hi All,

I have created a table . when I tried to create projection on the table it says "Nullable FKs are not allowed in projection definition".

Below is the procedure I followed.

Can any one help me out.

CREATE TABLE f1(a varchar(10), b varchar(10));
CREATE TABLE d1(a varchar(10), b varchar(10));
CREATE TABLE f1_staging(a varchar(10), b varchar(10));
ALTER TABLE d1 ADD CONSTRAINT d1_pk PRIMARY KEY (a, b);
ALTER TABLE f1 ADD CONSTRAINT f1_fk FOREIGN KEY (a, b) references d1 (a, b);
CREATE PROJECTION f1_super(a, b) AS SELECT * FROM f1 ORDER BY a, b;
CREATE PROJECTION d1_super(a, b) AS SELECT * FROM d1 ORDER BY a, b;
CREATE PROJECTION f1_staging_super(a, b) AS SELECT * FROM f1_staging ORDER BY a, b;
CREATE PROJECTION prejoin(f1_a, f1_b, d1_a, d1_b)
AS SELECT f1.a, f1.b, d1.a, d1.b
FROM f1 join d1 on f1.a=d1.a and f1.b=d1.b
ORDER BY d1.a, d1.b;

output of the projection created:

dbadmin=> create projection prejoin(f1_a,f1_b, d1_a, d1_b)
dbadmin-> as select f1.a,f1.b,d1.a,d1.b
dbadmin-> from f1 join d1 on f1.a=d1.a and f1.b=d1.b
dbadmin-> order by d1.a,d1.b;
WARNING 4486: Projections are always created and persisted in the default Vertica locale. The current locale is en_GB
ERROR 2005: Nullable FKs are not allowed in projection definition.


Thanks ,
Malar

[JimKnicely]

Hi,

Welcome to the forums!

When you create the f1 table you need to define the FK columns as being NOT NULL.

Example:

Code: Select all

dbadmin=> CREATE TABLE d1(a VARCHAR(10), b VARCHAR(10), PRIMARY KEY (a, b)) KSAFE 1;
CREATE TABLE
dbadmin=> CREATE TABLE f1(a VARCHAR(10) NOT NULL, b VARCHAR(10) NOT NULL) KSAFE 1;
CREATE TABLE
dbadmin=> ALTER TABLE f1 ADD CONSTRAINT f1_fk FOREIGN KEY (a, b) REFERENCES d1 (a, b);
ALTER TABLE
dbadmin=> CREATE PROJECTION prejoin(f1_a, f1_b, d1_a, d1_b)
dbadmin-> AS SELECT f1.a, f1.b, d1.a, d1.b
dbadmin-> FROM f1 join d1 on f1.a=d1.a and f1.b=d1.b
dbadmin-> ORDER BY d1.a, d1.b;
CREATE PROJECTION

I hope this helps!

[malargopal]

Hi Jim Knicely,

Thank you!!

I made it by your idea!!

Thanks,

Malar

[JimKnicely]

GREAT!