Обсуждение: help with referential integrity

I'm attempting, for the first time, to make use of referential integrity,
but I'm getting an error.

Warning: PostgreSQL query failed: ERROR: referential integrity violation -
key referenced from LessonsPages not found in Lessons

Here is the table's structures:

CREATE SEQUENCE "LessonsPages_id_seq";
CREATE TABLE "LessonsPages" (
    "id" int4 DEFAULT nextval('"LessonsPages_id_seq"') NOT NULL,
    "lid" int4 NOT NULL
            REFERENCES "Pages"
            ON DELETE CASCADE,
    "pid" int4 NOT NULL
            REFERENCES "Lessons"
            ON DELETE CASCADE,
    PRIMARY KEY ("lid", "pid")
);

CREATE SEQUENCE "Lessons_id_seq";
CREATE TABLE "Lessons" (
    "id" int4 nextval('"Lessons_id_seq"') NOT NULL,
    "page_name" char(64),
    "summary_email" bool,
    "order_num" int2,
    PRIMARY KEY ("id")
);

CREATE SEQUENCE "Pages_id_seq";
CREATE TABLE "Pages" (
    "id" int4 nextval('"Pages_id_seq"') NOT NULL,
    "page_name" char(64),
    "summary_email" bool,
    "order_num" int2,
    PRIMARY KEY ("id")
);

The error comes when:

INSERT INTO "Pages" (order_num) VALUES (1);
SELECT id FROM "Pages";
|    id    |
    16
1 Row

INSERT INTO "Lessons" (lesson_name, order_num) VALUES ('test lesson', 2);
SELECT id FROM "Lessons";
|     id    |
    9
1 Row

INSERT INTO "LessonsPages" (lid,pid) VALUES (9,16);


What am I doing wrong?

-          -          -          -          -          -          -
  -          -          -          -          -
WARNING: Some experts believe that use of any keyboard may cause serious
injury.
Consult Users Guide.
                                                                                                   dfunct@telus.net

On Mon, 11 Dec 2000, Soma Interesting wrote:

>
> I'm attempting, for the first time, to make use of referential integrity,
> but I'm getting an error.
>
> Warning: PostgreSQL query failed: ERROR: referential integrity violation -
> key referenced from LessonsPages not found in Lessons
>
> Here is the table's structures:
>
> CREATE SEQUENCE "LessonsPages_id_seq";
> CREATE TABLE "LessonsPages" (
>     "id" int4 DEFAULT nextval('"LessonsPages_id_seq"') NOT NULL,
>     "lid" int4 NOT NULL
>             REFERENCES "Pages"
>             ON DELETE CASCADE,
>     "pid" int4 NOT NULL
>             REFERENCES "Lessons"
>             ON DELETE CASCADE,
>     PRIMARY KEY ("lid", "pid")
> );

Did you really want lid referencing pages and
pid referencing lessons rather than the other
way around?  It seems below you're inserting
things the other way around.

> The error comes when:
>
> INSERT INTO "Pages" (order_num) VALUES (1);
> SELECT id FROM "Pages";
> |    id    |
>     16
> 1 Row
>
> INSERT INTO "Lessons" (lesson_name, order_num) VALUES ('test lesson', 2);
> SELECT id FROM "Lessons";
> |     id    |
>     9
> 1 Row
>
> INSERT INTO "LessonsPages" (lid,pid) VALUES (9,16);
>
>
> What am I doing wrong?
>
> -          -          -          -          -          -          -
>   -          -          -          -          -
> WARNING: Some experts believe that use of any keyboard may cause serious
> injury.
> Consult Users Guide.
>                                                                                                    dfunct@telus.net
>