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

Hi, I have a small and simple database MyStudents.db . It has three tables: *student*, *workpiecelist*, *uniqueworkpc*. How can I manage to get primary key (pk) automatically for *uniqueworkpc* table which is composed by pk of *student* table and pk of *workpiecelist* table like below? 03256789415

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" TEX

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 fie

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?

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, "cla

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, f

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] Is it possible to CREATE TABLE from other tables in a complex way?

R Smith-2 wrote > This seems like a whole assignment, and we are not in the habit to do > assignments for people, > > BUT, we can get you started down the path. > > You should know how to do all you are asking by simple RDBMS mechanics, > except maybe how to initialize a table with all dates an

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

R Smith-2 wrote > Here is a query that will produce all days of the year (without Sundays) > plus their week days (and I've expanded for lesson blocks too, but you > will probably need to add/edit as I don't know the exact values, but the > method should be clear). You can JOIN this to the other

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

R Smith-2 wrote > On 2018/03/17 12:40 PM, csanyipal wrote: >> R Smith-2 wrote >>> Here is a query that will produce all days of the year (without Sundays) >>> plus their week days (and I've expanded for lesson blocks too, but you >>> will probably need to

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

R Smith-2 wrote > On 2018/03/17 12:40 PM, csanyipal wrote: >> R Smith-2 wrote >>> Here is a query that will produce all days of the year (without Sundays) >>> plus their week days (and I've expanded for lesson blocks too, but you >>> will probably need to

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

David Raymond wrote > In the commented out section: > > TimeTable(DoWeek,Grade,Class_) AS > (VALUES('M'),(7),('b'),('M'),(5),('a'),('Tu'),(8),('c')... > > Shouldn't that be ...AS (VALUES ('M', 7, 'B'), ('M', 5, 'a'), ('Tu', 5, > 'c')...? > > WITH PAR(calStartDate, calEndDate) AS (SELECT '2017-09

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

R Smith-2 wrote > On 2018/03/21 9:58 PM, csanyipal wrote: >> >> I am really trying to understand how CTEs works and trying to achive my >> goal >> ( see bellow ) so I modified a little your code: >> ... >> As you can see I tried to add more CTEs into code out

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

csanyipal wrote > 2017-09-01|F|1-2|5|b > 2017-09-01|F|1-2|7|c > 2017-09-04|M|1-2|7|b > 2017-09-04|M|1-2|5|a > 2017-09-05|Tu|1-2|8|c > 2017-09-05|Tu|1-2|8|b > 2017-09-06|W|1-2|8|a > 2017-09-06|W|1-2|7|a > 2017-09-07|Th|1-2|6|a > 2017-09-07|Th|1-2|5|c > 2017-09-08

Re: [sqlite] Is it possible to CREATE TABLE from other tables in a complex way?

csanyipal wrote > csanyipal wrote > 2017-09-01|F|1-2|5|b > 2017-09-01|F|1-2|7|c > 2017-09-04|M|1-2|7|b > 2017-09-04|M|1-2|5|a > 2017-09-05|Tu|1-2|8|c > 2017-09-05|Tu|1-2|8|b > 2017-09-06|W|1-2|8|a > 2017-09-06|W|1-2|7|a > 2017-09-07|Th|1-2|6|a > 2017-09-07|Th|1-2|