Table creation and inserting values on not null filed

Data Modeling

Table creation and inserting values on not null filed

I have created a table and below is the table defination:

 

CREATE SET TABLE test_db_devl.cvs_provider ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT,
DEFAULT MERGEBLOCKRATIO
(
CVS_PRV_ID INTEGER NOT NULL,
APPL_PV_CNTRT_AFF_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
APPL_PV_NBR VARCHAR(20) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
)
UNIQUE PRIMARY INDEX CVS_PRV_i01 ( CVS_PRV_ID );

 

Now I have inserted values into table :

 

INSERT INTO test_db_devl.cvs_provider 
(CVS_PRV_ID, APPL_PV_CNTRT_AFF_NBR, APPL_PV_NBR)
VALUES ('1','','');

 

Now below two fields have single quotes:

APPL_PV_CNTRT_AFF_NBR,

APPL_PV_NBR

 

But there values are defiend as not null so what is the impact of that.

Fields are defined as not null and quotes work as null.

Can someone explain this scenario.

 

  • Teradata

Accepted Solutions
Junior Contributor

Re: Table creation and inserting values on not null filed

An empty string '' is not the same a NULL.

 

where APPL_PV_NBR = '' only returns a row with an empty string

where APPL_PV_NBR IS NULL only returns a row with a NULL

 

Just try it

1 ACCEPTED SOLUTION
4 REPLIES
Junior Contributor

Re: Table creation and inserting values on not null filed


quotes work as null.

Nope, only when you used to work with Oracle :-)

An empty string is a value which is different from a NULL in every DBMS supporting Stnadard SQL.

Re: Table creation and inserting values on not null filed

Hi,

 

I have craeted the table in teradata and table accept '' value.

So i am curious to know the difference between that not null field and field which is defiend as null.

Junior Contributor

Re: Table creation and inserting values on not null filed

An empty string '' is not the same a NULL.

 

where APPL_PV_NBR = '' only returns a row with an empty string

where APPL_PV_NBR IS NULL only returns a row with a NULL

 

Just try it

Re: Table creation and inserting values on not null filed

Thank You very Much, now i got it.