CODEWITHSUNDEEP
sqlitelimit
ProfileForStack4
ProfileForStack4

Reputation: 47

How to limit numeric column type to certain symbols before and after decimal separator in sqlite?

I want to limit numeric column type to 10 symbols before decimal separator and 4 symbols after decimal separator. I executed the following command:

ALTER TABLE scustdisc ADD COLUMN spec_price numeric(10,4)

The command executed without errors but when I try to insert value in spec_price 10.123456 I am able to do it. It should give error and the value not to be inserted. Am I wrong in my alter command?

Upvotes: 0

Views: 40

Answers (1)

MikeT
MikeT

Reputation: 56938

SQLite has a dynamic type system and the column types have a limited impact, but can be virtually any name. They are resolved to one of TEXT, NUMERIC, INTEGER, REAL or BLOB.

  • numeric(0,0) - numeric(99999999,99999999) and more resolve to NUMERIC.

As such 10,4 4,10 etc means nothing and makes no difference to SQLite.

With one exception bar constraints a column may hold any type of value. The column type only comes into play in determining the way the data is stored.

A must read is Datatypes In SQLite Version 3

You may also find How flexible/restricive are SQLite column types?

You may be able to resolve this by using a CHECK constraint CREATE TABLE or by using a TRIGGER or multiple TRIGGERs.

You could format the number(s) appropriately when they are displayed.

You could utilise the round(x,y) function Core Functions

Upvotes: 1

Related Questions