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 <ikoro...@gmail.com> wrote:
> Hi,
>
>
>
> On Oct 21, 2017 5:18 AM, "csanyipal" <csanyi...@gmail.com> 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
