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

[csanyipal]

> 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/
  
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[csanyipal]

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

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

[csanyipal]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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



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

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

[Simon Slavin]

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



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

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

[csanyipal]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[csanyipal]

> 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[csanyipal]

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 wprokpiece of a student.

So that is why I am searching for a solution where a piece of code would
fill out for me the 'id' and 'student' fields in the '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

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

[Simon Slavin]

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  wrote:

> 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 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Igor Korot]

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 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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[csanyipal]

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")*

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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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  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 
> 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:sqlite-users-boun...@mailinglists.sqlite.org]
> > On Behalf Of csanyipal
> > Sent: Friday, October 20, 2017 3:57 PM
> > To: sqlite-users@mailinglists.sqlite.org
> > 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
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> > ___
> > sqlite-users mailing list
> > sqlite-users@mailinglists.sqlite.org
> > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> >
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Eugene Mirotin]

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 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:sqlite-users-boun...@mailinglists.sqlite.org]
> On Behalf Of csanyipal
> Sent: Friday, October 20, 2017 3:57 PM
> To: sqlite-users@mailinglists.sqlite.org
> 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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
> ___
> sqlite-users mailing list
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
>
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

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

[Darko Volaric]

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  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
> sqlite-users@mailinglists.sqlite.org
> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

Re: [sqlite] 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:sqlite-users-boun...@mailinglists.sqlite.org] On 
Behalf Of csanyipal
Sent: Friday, October 20, 2017 3:57 PM
To: sqlite-users@mailinglists.sqlite.org
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
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
___
sqlite-users mailing list
sqlite-users@mailinglists.sqlite.org
http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users