Support Questions
Find answers, ask questions, and share your expertise

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

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.

; ;