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

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
17 messages Options
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
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?
03256789415632-2
where
03256789415632
is a pk of a student in *student* table, and
2
is an id of a workpiece in *workpiecelist* table.



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

David Raymond
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,
  workpiecelist_id int references workpieceList,
  blah,
  primary key (student_id, workpiecelist_id)
);



-----Original Message-----
From: sqlite-users [mailto:[hidden email]] On Behalf Of csanyipal
Sent: Friday, October 20, 2017 3:57 PM
To: [hidden email]
Subject: [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?
03256789415632-2
where
03256789415632
is a pk of a student in *student* table, and
2
is an id of a workpiece in *workpiecelist* table.



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Darko Volaric
In reply to this post by csanyipal
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 <[hidden email]> wrote:
>
> 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?
> 03256789415632-2
> where
> 03256789415632
> is a pk of a student in *student* table, and
> 2
> is an id of a workpiece in *workpiecelist* table.
>
>
>
> -----
> Best, Pál
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Eugene Mirotin
In reply to this post by David Raymond
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 <[hidden email]>
wrote:

> 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,
>   workpiecelist_id int references workpieceList,
>   blah,
>   primary key (student_id, workpiecelist_id)
> );
>
>
>
> -----Original Message-----
> From: sqlite-users [mailto:[hidden email]]
> On Behalf Of csanyipal
> Sent: Friday, October 20, 2017 3:57 PM
> To: [hidden email]
> Subject: [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?
> 03256789415632-2
> where
> 03256789415632
> is a pk of a student in *student* table, and
> 2
> is an id of a workpiece in *workpiecelist* table.
>
>
>
> -----
> Best, Pál
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

J Decker
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 cell along
   the Z-curve.

doesn't have to be base 2 merging, could use base10 digits and merge them
interleaving....


(a,b) = (123, 567)
merged = 152637

Although for simpliicty simply combining them with a separator character
would work too....

if you don't have a separator (1,111) would look the same as (111,1)

On Fri, Oct 20, 2017 at 1:17 PM, Eugene Mirotin <[hidden email]> wrote:

> 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 <[hidden email]>
> wrote:
>
> > 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,
> >   workpiecelist_id int references workpieceList,
> >   blah,
> >   primary key (student_id, workpiecelist_id)
> > );
> >
> >
> >
> > -----Original Message-----
> > From: sqlite-users [mailto:[hidden email]]
> > On Behalf Of csanyipal
> > Sent: Friday, October 20, 2017 3:57 PM
> > To: [hidden email]
> > Subject: [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?
> > 03256789415632-2
> > where
> > 03256789415632
> > is a pk of a student in *student* table, and
> > 2
> > is an id of a workpiece in *workpiecelist* table.
> >
> >
> >
> > -----
> > Best, Pál
> > --
> > Sent from: http://sqlite.1065341.n5.nabble.com/
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > _______________________________________________
> > sqlite-users mailing list
> > [hidden email]
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
This post was updated on .
In reply to this post by Darko Volaric
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" TEXT NOT NULL
);

CREATE TABLE "workpiecelist" (
  "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
  "grade" TINYINT,
  "quarter" TINYINT,
  "workpiecenamehu" TEXT NOT NULL,
  "workpiecenamesr" TEXT NOT NULL
);

CREATE TABLE "uniqueworkpiece" (
  "student" TEXT NOT NULL REFERENCES "student" ("idnum"),
  "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id"),
  "mark" TINYINT,
  "cause" TEXT NOT NULL,
  CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student", "workpiece_list")
);

CREATE INDEX "idx_uniqueworkpiece__workpiece_list" ON "uniqueworkpiece"
("workpiece_list")

Now when I want to fill the 'uniqueworkpiece' table with data, must I manually enter in values in to all four columns of this table, like this below:
1. Should I enter as a value in the 'student' column an 'idnum' of a student from the 'student' table?
2. Should I enter as a value in the 'workpiece_list' column an 'id' of a workpiecelist from the 'workpiecelist' table?
3. I know I must enter values in to 'mark' and 'cause' columns of the 'uniqueworkpiece' table.

Or can I somehow get filled automatically the first two columns of 'uniqueworkpiece' table?

What I want is to get the N number of records in the 'uniqueworkpiece' table where the N number is the number of records in the 'workpiecelist' table in which 'grade' value is '5' in this case:
( 'grade' column in 'workpiecelist' and 'student' tables must be the same, eg. 5 for every unique workpiece )
student | workpiece_list | mark | cause
idnum1 | 1 |  |  |
idnum2 | 2 |  |  |
...
idnumN| N |  |  |

So when I get these records atuomatically for every student from the 'student' table, for every 'grade' and every 'workpiece' from the 'workpiecelist' table, then just keep filling the other two columns of 'uniqueworkpiece' table, the 'mark' and 'cause'.

I hope this above is understandable, because my first language is not English.

Here is an image that represent a LibreOffice Calc spreed sheet's sheet in which I begin to enter records for the first two student who are going in the same school class, say '5a' where '5' is the grade, and 'a' is the class.
uniqueworkpiece_table

-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

Igor Korot
Hi,



On Oct 21, 2017 5:18 AM, "csanyipal" <[hidden email]> 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 NULL,
  "grade" TINYINT,
  "classname" VARCHAR(1) NOT NULL,
  "formmaster" TEXT NOT NULL
);

CREATE TABLE "workpiecelist" (
  "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
  "grade" TINYINT,
  "quarter" TINYINT,
  "workpiecenamehu" TEXT NOT NULL,
  "workpiecenamesr" TEXT NOT NULL
);

CREATE TABLE "uniqueworkpiece" (
  "student" TEXT NOT NULL REFERENCES "student" ("idnum"),
  "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id"),
  "mark" TINYINT,
  "cause" TEXT NOT NULL,
  CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student", "workpiece_list")
);


You can as well drop the PK on the table above and live with just 2 FK
constraints. If you really want a PK on this table you can create a dummy
field and make it a primary key.

Other than that you should be good.

Thank you.


CREATE INDEX "idx_uniqueworkpiece__workpiece_list" ON "uniqueworkpiece"
("workpiece_list")*

What do you think, did I achieve what I want?



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Don V Nielsen
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 work piece can have one and only one
student? Do you need a unique constraint on both student>work piece and
work piece>student?

On Sat, Oct 21, 2017 at 12:09 PM, Igor Korot <[hidden email]> wrote:

> Hi,
>
>
>
> On Oct 21, 2017 5:18 AM, "csanyipal" <[hidden email]> 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 NULL,
>   "grade" TINYINT,
>   "classname" VARCHAR(1) NOT NULL,
>   "formmaster" TEXT NOT NULL
> );
>
> CREATE TABLE "workpiecelist" (
>   "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
>   "grade" TINYINT,
>   "quarter" TINYINT,
>   "workpiecenamehu" TEXT NOT NULL,
>   "workpiecenamesr" TEXT NOT NULL
> );
>
> CREATE TABLE "uniqueworkpiece" (
>   "student" TEXT NOT NULL REFERENCES "student" ("idnum"),
>   "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id"),
>   "mark" TINYINT,
>   "cause" TEXT NOT NULL,
>   CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student", "workpiece_list")
> );
>
>
> You can as well drop the PK on the table above and live with just 2 FK
> constraints. If you really want a PK on this table you can create a dummy
> field and make it a primary key.
>
> Other than that you should be good.
>
> Thank you.
>
>
> CREATE INDEX "idx_uniqueworkpiece__workpiece_list" ON "uniqueworkpiece"
> ("workpiece_list")*
>
> What do you think, did I achieve what I want?
>
>
>
> -----
> Best, Pál
> --
> Sent from: http://sqlite.1065341.n5.nabble.com/
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> _______________________________________________
> sqlite-users mailing list
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
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 declared as TEXT NOT NULL COLLATE NOCASE.  This will simplify your programming later.

Why is this field

>  "idnum" TEXT NOT NULL CONSTRAINT "pk_student" PRIMARY KEY,

declared as TEXT when it has 'num' in the name ?

Although it will work, do not do this:

>  CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student", "workpiece_list")

instead allow that table to have

        INTEGER PRIMARY KEY AUTOINCREMENT

like your workpiecelist table, and declare a UNIQUE index to enforce uniqueness.  This allows you to make changes without having SQLite complain about duplication in the primary key.

Simon.
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
This post was updated on .
In reply to this post by Don V Nielsen
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 also need to ensure that the work piece can have one and only one
> student? Do you need a unique constraint on both student>work piece and
> work piece>student?

Yes, I have students.
Every student belong to one grade and to one class, say 5a where 5 is the
grade ans 'a' is the class.
In the table 'workpiecelist' are workpieces listed, say in grade 5 there are
13 different workpieces out there, in grade 6 there are 14 different
workpieces, in grade 7 there are 7, and in grade 8 there are 14 workpieces
listed.
So, one student in ( or at? ) grade 5 during a school year must to make all
those 13 workpieces, but in the grade 5 class 'a' there are 19 students, and
every one of them must to make 13 workpieces. So I must have the
'uniqueworkpiece' table to associate every student to every workpiece what
that student must to make.

So, the 'uniqueworkpiece' table will have few hundred records.
So it would be for me easier to fill out the 'uniqueworkpiece' table if I
have to enter manually only 'mark' and 'cause' fields, when I am being
examine a unique workpiece of a student.

So that is why I am searching for a solution where a piece of code would
fill out for me the 'student' and 'workpiece_list' fields in the 'uniqueworkpiece'
table. The 'id' field is autoincrement so it is not need to be entered.

-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
In reply to this post by Simon Slavin-3

> 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 declared as TEXT NOT NULL COLLATE
> NOCASE.  This will simplify your programming later.

Understood.

> Why is this field
>
>>  "idnum" TEXT NOT NULL CONSTRAINT "pk_student" PRIMARY KEY,
>
> declared as TEXT when it has 'num' in the name ?
>
> Although it will work, do not do this:
>
>>  CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY ("student",
>> "workpiece_list")
>
> instead allow that table to have
>
>         INTEGER PRIMARY KEY AUTOINCREMENT
>
> like your workpiecelist table, and declare a UNIQUE index to enforce
> uniqueness.  This allows you to make changes without having SQLite
> complain about duplication in the primary key.

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.



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
In reply to this post by Simon Slavin-3
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 NOCASE,
  "grade" INTEGER,
  "classname" TEXT NOT NULL,
  "formmaster" TEXT
);
CREATE TABLE IF NOT EXISTS "workpiecelist" (
  "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
  "grade" INTEGER,
  "quarter" INTEGER,
  "workpiecenamehu" TEXT NOT NULL COLLATE NOCASE,
  "workpiecenamesr" TEXT NOT NULL COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS "uniqueworkpiece" (
  "id" INTEGER CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY AUTOINCREMENT,
  "student" TEXT NOT NULL REFERENCES "student" ("idchr"),
  "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id"),
  "mark" INTEGER,
  "cause" TEXT
);



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

Simon Slavin-3
In reply to this post by csanyipal


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

>  "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
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

Richard Damon
On 10/23/17 12:26 PM, Simon Slavin wrote:

>
> On 23 Oct 2017, at 4:25pm, csanyipal <[hidden email]> 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
> [hidden email]
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users


--
Richard Damon

_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
In reply to this post by Simon Slavin-3
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,
  "classname" TEXT NOT NULL,
  "formmaster" TEXT
);
CREATE TABLE IF NOT EXISTS "workpiecelist" (
  "id" INTEGER CONSTRAINT "pk_workpiecelist" PRIMARY KEY AUTOINCREMENT,
  "grade" INTEGER,
  "quarter" INTEGER,
  "workpiecenamehu" TEXT NOT NULL COLLATE NOCASE,
  "workpiecenamesr" TEXT NOT NULL COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS "uniqueworkpiece" (
  "id" INTEGER CONSTRAINT "pk_uniqueworkpiece" PRIMARY KEY AUTOINCREMENT,
  "student" TEXT NOT NULL REFERENCES "student" ("idchr") ON DELETE CASCADE
ON UPDATE CASCADE ,
  "workpiece_list" INTEGER NOT NULL REFERENCES "workpiecelist" ("id") ON
DELETE CASCADE ON UPDATE CASCADE,
  "mark" INTEGER,
  "cause" TEXT
);



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
This post was updated on .
In reply to this post by Richard Damon
So here it is:
CREATE TABLE IF NOT EXISTS student (
  id INTEGER CONSTRAINT pk_student PRIMARY KEY AUTOINCREMENT,
  idnum INTEGER UNIQUE NOT NULL,
  studentname TEXT NOT NULL COLLATE NOCASE,
  teachinglang TEXT NOT NULL COLLATE NOCASE,
  grade INTEGER,
  classname TEXT NOT NULL,
  formmaster TEXT
);
CREATE TABLE IF NOT EXISTS workpiecelist (
  id INTEGER CONSTRAINT pk_workpiecelist PRIMARY KEY AUTOINCREMENT,
  grade INTEGER,
  quarter INTEGER,
  workpiecenamehu TEXT NOT NULL COLLATE NOCASE,
  workpiecenamesr TEXT NOT NULL COLLATE NOCASE
);
CREATE TABLE IF NOT EXISTS uniqueworkpiece (
  id INTEGER CONSTRAINT pk_uniqueworkpiece PRIMARY KEY AUTOINCREMENT,
  student TEXT NOT NULL REFERENCES student (idnum) ON DELETE CASCADE ON
UPDATE CASCADE ,
  workpiece_list INTEGER NOT NULL REFERENCES workpiecelist (id) ON DELETE
CASCADE ON UPDATE CASCADE,
  mark INTEGER,
  cause TEXT
);



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál
Reply | Threaded
Open this post in threaded view
|

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

csanyipal
In reply to this post by Simon Slavin-3

> 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/
<http://zetcode.com/db/sqlitepythontutorial/>  
I think I could this way add few hundreds of rows to the 'uniqueworkpiece'
by running such a script.
Thank you all for help on this thread!



-----
Best, Pál
--
Sent from: http://sqlite.1065341.n5.nabble.com/
_______________________________________________
sqlite-users mailing list
[hidden email]
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
Best, Pál