Re: [sqlite] How to create primary key from two another PK's?

> Your TEXT NOT NULL fields should be declared as TEXT NOT NULL COLLATE > NOCASE. This will simplify your programming later. I think I am going to write python scripts to use those with my database. For start I find this: http://zetcode.com/db/sqlitepythontutorial/

Re: [sqlite] How to create primary key from two another PK's?

So here it is: CREATE TABLE IF NOT EXISTS student ( id INTEGER CONSTRAINT pk_student PRIMARY KEY AUTOINCREMENT, idnum INTEGER UNIQUE NOT NULL COLLATE NOCASE, studentname TEXT NOT NULL COLLATE NOCASE, teachinglang TEXT NOT NULL COLLATE NOCASE, grade INTEGER, classname TEXT NOT NULL,

Re: [sqlite] How to create primary key from two another PK's?

I added CASCADE, like this: CREATE TABLE IF NOT EXISTS "student" ( "id" INTEGER CONSTRAINT "pk_student" PRIMARY KEY AUTOINCREMENT, "idchr" TEXT UNIQUE NOT NULL COLLATE NOCASE, "studentname" TEXT NOT NULL COLLATE NOCASE, "teachinglang" TEXT NOT NULL COLLATE NOCASE, "grade" INTEGER,

Re: [sqlite] How to create primary key from two another PK's?

On 10/23/17 12:26 PM, Simon Slavin wrote: On 23 Oct 2017, at 4:25pm, csanyipal 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

Re: [sqlite] How to create primary key from two another PK's?

On 23 Oct 2017, at 4:25pm, csanyipal 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

Re: [sqlite] How to create primary key from two another PK's?

I modified my database so it is now like: BEGIN TRANSACTION; CREATE TABLE IF NOT EXISTS "student" ( "id" INTEGER CONSTRAINT "pk_student" PRIMARY KEY AUTOINCREMENT, "idchr" TEXT UNIQUE NOT NULL COLLATE NOCASE, "studentname" TEXT NOT NULL COLLATE NOCASE, "teachinglang" TEXT NOT NULL COLLATE

Re: [sqlite] How to create primary key from two another PK's?

> SQLite does not support VARCHAR(2). All fields declared like that are > TEXT and SQLite pays no attention to the length of the text. Declare them > as TEXT. > > SQLite does not support TINYINT All fields declared like that are > INTEGER. Declare them as INTEGER. > > Your TEXT NOT NULL

Re: [sqlite] How to create primary key from two another PK's?

Don V Nielsen wrote > Just asking some leading questions. You have students. And students have > work pieces. You are then creating a list "uniqueworkpiece" showing the > work pieces associated to each student. Your primary key will ensure the > uniqueness of the student to work piece. > > Do you

Re: [sqlite] How to create primary key from two another PK's?

SQLite does not support VARCHAR(2). All fields declared like that are TEXT and SQLite pays no attention to the length of the text. Declare them as TEXT. SQLite does not support TINYINT All fields declared like that are INTEGER. Declare them as INTEGER. Your TEXT NOT NULL fields should be

Re: [sqlite] How to create primary key from two another PK's?

Just asking some leading questions. You have students. And students have work pieces. You are then creating a list "uniqueworkpiece" showing the work pieces associated to each student. Your primary key will ensure the uniqueness of the student to work piece. Do you also need to ensure that the

Re: [sqlite] How to create primary key from two another PK's?

Hi, On Oct 21, 2017 5:18 AM, "csanyipal" wrote: I try to follow advices and modify my database so it is now like this: *CREATE TABLE "student" ( "idnum" TEXT NOT NULL CONSTRAINT "pk_student" PRIMARY KEY, "studentname" TEXT NOT NULL, "teachinglang" VARCHAR(2) NOT

Re: [sqlite] How to create primary key from two another PK's?

I try to follow advices and modify my database so it is now like this: *CREATE TABLE "student" ( "idnum" TEXT NOT NULL CONSTRAINT "pk_student" PRIMARY KEY, "studentname" TEXT NOT NULL, "teachinglang" VARCHAR(2) NOT NULL, "grade" TINYINT, "classname" VARCHAR(1) NOT NULL, "formmaster"

Re: [sqlite] How to create primary key from two another PK's?

Or you could do something really fancy https://en.wikipedia.org/wiki/Z-order_curve http://www.forceflow.be/2013/10/07/morton-encodingdecoding-through-bit-interleaving-implementations/ - (x,y,z) = *(5,9,1)* = (0101,1001,0001) - Interleaving the bits results in: 010001000111 = *1095* th

Re: [sqlite] How to create primary key from two another PK's?

Yeah, use two FKs, then you can obtain this "combined" value on select: select printf("%s-%s", student_id, workpiecelist_id) as id from uniqueworkpc On Fri, Oct 20, 2017 at 11:05 PM David Raymond wrote: > I don't know about automatically, but you can use foreign keys

Re: [sqlite] How to create primary key from two another PK's?

You don't, that's not how relational databases work. You need to create a separate field for each foreign key (student and workpiecelist) and together they form the primary key for the uniqueworkpc table. See David's reply for details. > On Oct 20, 2017, at 9:56 PM, csanyipal

Re: [sqlite] How to create primary key from two another PK's?

I don't know about automatically, but you can use foreign keys to help. create table student ( student_id integer primary key, blah ); create table workpiecelist ( workpiecelist_id integer primary key, blah ); create table uniqueworkpc ( student_id int references student,