Support Questions
Find answers, ask questions, and share your expertise
Announcements
Alert: Welcome to the Unified Cloudera Community. Former HCC members be sure to read and learn how to activate your account here.

Is it possible to have non nullable columns that are not a primary key (Phoenix 4.4)

Highlighted

Is it possible to have non nullable columns that are not a primary key (Phoenix 4.4)

New Contributor

Hi,

I'm using Phoenix 4.4 (HDP 2.4). I want to change some columns in an existing table to be not nullable. I was able to change the nullable property in system.catalog, but I can still upsert nulls into the changed columns.

Here's an example of what I did:

Create a simple test table

CREATE TABLE IF NOT EXISTS TEST_S.TEST_T(
ROWKEY VARCHAR NOT NULL PRIMARY KEY,
TEST_C BIGINT)

Change nullable to 0 (also tried with is_nullable)

UPSERT INTO SYSTEM.CATALOG (TABLE_SCHEM, TABLE_NAME, COLUMN_NAME, COLUMN_FAMILY, NULLABLE, IS_NULLABLE) 
VALUES ('TEST_S','TEST_T','TEST_C', '0', 0, 'FALSE')

Upsert values into the table

UPSERT INTO TEST_S.TEST_T (ROWKEY, TEST_C)
VALUES ('TEST1', NULL)

The above upsert goes through without any error or warning. When I query the table, I can see null values in the TEST_C column.

I suppose it is related to the fact that it's not possible to execute a create statement like below (neither with or without trying to make TEST_C a PK)

CREATE TABLE IF NOT EXISTS TEST_S.TEST_T(
ROWKEY VARCHAR NOT NULL PRIMARY KEY,
TEST_C BIGINT NOT NULL --PRIMARY KEY
)

Does anybody know if it's possible to work around this problem in Phoenix 4.4 or if the newer versions allow to do such a thing?