Teradata Error Precedence

Database

Teradata Error Precedence

I have a simple question: In case Teradata RDBMS Engine finds multiple runtime errors while executing a SQL then which Error Code does it display?

For example: I have a table T1 having two columns C1 and C2. C1 is Unique field and C2 is NOT NULL field. If I try to insert one row into T1 in which C1 is having duplicate data and C2 is NULL then should the RDBMS throw NOT NULL Constraint violation error or Duplicate Value in Unique Field error? Why? Is there common pattern in each RDBMS or does this vary with database.

Best Regards
Arnab Choudhury
2 REPLIES
Enthusiast

Re: Teradata Error Precedence

At the first -

if you try to insert ONE ROW (Table is empty) with Null in NOT NULL Column - then Unique constraint error will not come at all - not null error will come.

if you try to insert ONE ROW (Table has rows) with Same data in first column and Null in second column- then at first not null error will come - Because before inserting duplicate rows Parser will check constraint - thats why it'll throw that error (Null in NOT NULL) first.

Regards,

ANIMESH DUTTA

Re: Teradata Error Precedence

Normally most Databases evaluate a DML statement in the following order:

Before Insert, Update row level trigger errors.
Unique Constraints.
Check Constraints.
Foreign Keys.
After Insert, Update row level trigger errors.

In general all RDBMS should throw Duplicate Value in Unique field error first and then only throw other errors like NOT NULL. That's why I got confused when I encountered the NOT NULL error.

thanks for your clarification.

Thanks & Regards
Arnab