On 10/23/17 12:26 PM, Simon Slavin wrote:
On 23 Oct 2017, at 4:25pm, csanyipal <csanyi...@gmail.com> wrote:
I will in the 'student' table allow an 'id' INTEGER PRIMARY KEY
AUTOINCREMENT .
Every student have an identification number and such a number is 13 digit
long. But some idnumber start with leading zero so I think to it is better
to have this filed as TEXT to keep those leading zeros at the beginning of
the 'idnum'. I do not want to do calculations with 'idnum' values.
That is a good clear explanation. If you have something that looks like digits
with a leading zero is it not a number, it is TEXT. You should handle this
field as TEXT only. You cannot use INTEGER or AUTOINCREMENT with it. Your
most recent change, to having two fields,
Slight disagreement, if it is always 13 digits, and zero filled to reach
that length, that can still be an integer, you just need to make sure
your presentation layer displays the number with a zero (instead of a
blank) fill. If it was something like 0100 was one valid number, and
00100 was another, then an integer isn't viable. and INTEGER field
should be slightly more efficient (largely because it is shorter) then a
text field. A much bigger factor would be what is it likely to change to
in the future. If it might add another digit in the future (adding a
leading zero to all existing numbers) then the integer field is the way
to go. If you might let other characters in, then using text makes a lot
of sense.
"id" INTEGER CONSTRAINT "pk_student" PRIMARY KEY AUTOINCREMENT,
"idchr" TEXT UNIQUE NOT NULL COLLATE NOCASE,
shows you understand this and is going to give you good results.
On a separate matter, when you define your foreign keys, you should use CASCADE, so that if, for example, you make a correction to a value
in "student"."idchr" it automatically changes the values in "uniqueworkpiece"."student". And also
if you delete an entry in "workpiecelist" it automatically deletes the related entries in "uniqueworkpiece".
See section 4.3 of
<https://sqlite.org/foreignkeys.html>
By the way it is not necessary to double-quote entity names in SQLite. You can
do it if you like, but you don’t have to. But whichever you do you must be
consistent.
Simon.
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
--
Richard Damon
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
