CODEWITHSUNDEEP
sqlpython-3.xsqlite
Qwertford
Qwertford

Reputation: 1189

sqlite allows char(p) inputs exceeding length p

i defined a table as:

CREATE TABLE Stocks (
name CHAR(24),
price CHAR(22),
symbol CHAR(3), 
PRIMARY KEY name);

Specifically, symbol should be a 3 letter string such as "AUS". However, sqlite3 still allows me to insert symbols of length greater than 3 such as "Australia". Why isn't the 3 char data type enforced on input?

Upvotes: 1

Views: 398

Answers (2)

dmg
dmg

Reputation: 4481

Think of sqlite as the "dynamic typed" version of SQL.

From its documentation: "SQLite supports the concept of "type affinity" on columns. The type affinity of a column is the recommended type for data stored in that column. The important idea here is that the type is recommended, not required. Any column can still store any type of data."

Unless you add check constraints, there are no checks when you update/insert a value. It will simply accept the new one.

In fact, you don't have to specify types in the create table. Type affinity creates some problems during some operations, like order by.

For example:

sqlite> create table R(a);

sqlite> insert into r values (1), (-5), (3);
sqlite> select * from r order by a;
a 
--
-5
1 
3

and after:

sqlite> insert into r values ('aaaa');
sqlite> insert into r values ('000');
sqlite> select * from R order by a;
a 
--
-5
1 
3 
000 
aaaa

so it pays to cast values in sqlite:

sqlite> select * from r order by cast(a as int);
a 
--
-5
000 
aaaa 
1 
3

Note that the 'aaaa' gets converted to 0. There might be a way to convert it to NULL, but that is left to the reader as an exercise.

Upvotes: 2

forpas
forpas

Reputation: 164089

There is no CHAR data type in SQLite.
Actually you can use (almost) anything as a data type for a column and SQLite will not complain.

You can read more about SQLite's data types.

If you want a max length respected for string columns, define them as TEXT and set a CHECK constraint:

CREATE TABLE Stocks (
  name TEXT CHECK(LENGTH(name) <= 24),
  price TEXT CHECK(LENGTH(price) <= 22),
  symbol TEXT CHECK(LENGTH(symbol) <= 3), 
  PRIMARY KEY (name)
);

See a simplified demo.

Upvotes: 0

Related Questions