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)

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

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?

1 REPLY 1
Highlighted

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

New Contributor

If you can clone table, add the particular things and re direct to the cloned table may work. 

Let me know how it was ressolved.

Don't have an account?
Coming from Hortonworks? Activate your account here