Thread: Foreign keys

Foreign keys

From
"Jacob Vennervald Madsen"
Date:
Hi All

I'm having a problem with Postgresql v. 7.2.
When I create a table and add a foreign key as a table constraint I get
a parse error, but when I add it as a column constraint it works.
This is what I've tried:

CREATE TABLE "alarm_schedule" (
   "alarm_schedule_id" SERIAL NOT NULL,
   "day_of_week" int2 NOT NULL,
   "user_id" int4 NOT NULL,
   "send_email" bool NOT NULL,
   "send_sms" bool NOT NULL,
   PRIMARY KEY ("alarm_schedule_id"),
   FOREIGN KEY "user_id" REFERENCES smartlight_user ("user_id")
);

I get this error:
2002-04-22 13:04:53 [21193]  ERROR:  parser: parse error at or near """
ERROR:  parser: parse error at or near """

But when I do this:

CREATE TABLE "alarm_schedule" (
   "alarm_schedule_id" SERIAL NOT NULL,
   "day_of_week" int2 NOT NULL,
   "user_id" int4 NOT NULL REFERENCES smartlight_user ("user_id"),
   "send_email" bool NOT NULL,
   "send_sms" bool NOT NULL,
   PRIMARY KEY ("alarm_schedule_id")
);

It works.

Any ideas?

Note:    If you receive this twice it's because I sent it to
pgsql-general@postgres.org first.
But it's been hours since I sent it so I figured I'd try
pgsql-general@postgresql.org instaed.


Best regards,
Jacob Vennervald Madsen
Systems Developer

GoPinocchio
Norrebrogade 45
DK-2200 Copenhagen
www.gopinocchio.com
+45 26750106

**********************************************************************
This email and any files transmitted with it are confidential and
intended solely for the use of the individual or entity to whom they
are addressed. If you have received this email in error please notify
the system manager.


Re: Foreign keys

From
Devrim GUNDUZ
Date:
Hi,

On Mon, 22 Apr 2002, Jacob Vennervald Madsen wrote:

> CREATE TABLE "alarm_schedule" (
>    "alarm_schedule_id" SERIAL NOT NULL,
>    "day_of_week" int2 NOT NULL,
>    "user_id" int4 NOT NULL,
>    "send_email" bool NOT NULL,
>    "send_sms" bool NOT NULL,
>    PRIMARY KEY ("alarm_schedule_id"),
>    FOREIGN KEY "user_id" REFERENCES smartlight_user ("user_id")
> );

This works:

CREATE TABLE "alarm_schedule" (
   "alarm_schedule_id" SERIAL NOT NULL,
   "day_of_week" int2 NOT NULL,
   "user_id" int4 NOT NULL,
   "send_email" bool NOT NULL,
   "send_sms" bool NOT NULL,
   PRIMARY KEY ("alarm_schedule_id"),
   FOREIGN KEY ("user_id") REFERENCES smartlight_user(user_id)
);


Just a parenthesis ;)

Best regards.

--

Devrim GUNDUZ

devrim@oper.metu.edu.tr
devrim.gunduz@linux.org.tr
devrimg@tr.net

Web : http://devrim.oper.metu.edu.tr
------------------------------------------------------------------



Re: Foreign keys

From
Tom Lane
Date:
Devrim GUNDUZ <devrim@oper.metu.edu.tr> writes:
> Just a parenthesis ;)

Hmm, I wonder why the parse error message is so unhelpful?  If I write
the same example without double-quoting user_id, I get

    ERROR:  parser: parse error at or near "user_id"

which might at least offer a clue.

            regards, tom lane