Postgres Pro
Downloads
Home   >   mailing lists

Thread:

From
M C
Date:
Hi All,

I'm having trouble getting my head round setting up a composite foreign key. For example, consider the following:

CREATE TABLE sensorID (
        SensorID     VARCHAR (30) PRIMARY KEY
);

CREATE TABLE time (
        SensorID  VARCHAR (30) references sensorID(SensorID),
        Time      time without time zone,
        Date      date,
        CONSTRAINT Time_Pkey PRIMARY KEY (SensorID, Time, Date)
);


So far so good. However, I now want to set up another table that uses time.Time_Pkey as its foreign key, but I can't see how to do it. For example, something like:

CREATE TABLE notification (
        Time_Fkey ??????
        FOREIGN KEY (Time_Fkey) references time(Time_Pkey),
        Reason    VARCHAR (30)
);

What needs to go into "??????"  ? Can I even do something like this? Or can I do it with indexes? If so, help please?

Regards,
m
Do you have a story that started on Hotmail? Tell us now

Re:

From
"A. Kretschmer"
Date:
In response to M C :
> CREATE TABLE time (
>         SensorID  VARCHAR (30) references sensorID(SensorID),
>         Time      time without time zone,
>         Date      date,
>         CONSTRAINT Time_Pkey PRIMARY KEY (SensorID, Time, Date)
> );
>
>
> So far so good. However, I now want to set up another table that uses
> time.Time_Pkey as its foreign key, but I can't see how to do it. For example,
> something like:
>
> CREATE TABLE notification (
>         Time_Fkey ??????
>         FOREIGN KEY (Time_Fkey) references time(Time_Pkey),
>         Reason    VARCHAR (30)
> );
>
> What needs to go into "??????"  ? Can I even do something like this? Or can I
> do it with indexes? If so, help please?

test=*# CREATE TABLE notification ( SensorID  VARCHAR (30), Time time without time zone, Date      date, foreign
key(SensorID,Time, Date) references time); 
CREATE TABLE


Regards, Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47150,   D1: 0160/7141639 (mehr: -> Header)
GnuPG: 0x31720C99, 1006 CCB4 A326 1D42 6431  2EB0 389D 1DC2 3172 0C99

Re:

From
Jayadevan M
Date:
> I'm having trouble getting my head round setting up a composite foreign key. For example, consider the following:

> CREATE TABLE sensorID (
>         SensorID     VARCHAR (30) PRIMARY KEY
> );

> CREATE TABLE time (
>         SensorID  VARCHAR (30) references sensorID(SensorID),
>         Time      time without time zone,
>         Date      date,
>         CONSTRAINT Time_Pkey PRIMARY KEY (SensorID, Time, Date)
> );


>  So far so good. However, I now want to set up another table that uses time.Time_Pkey as its foreign key, but I can't see how to do it. For example, something like:

> CREATE TABLE notification (
>         Time_Fkey ??????
>         FOREIGN KEY (Time_Fkey) references time(Time_Pkey),
>         Reason    VARCHAR (30)
> );

> What needs to go into "??????"  ? Can I even do something like this? Or can I do it with indexes? If so, help please?


Something like this?
CREATE TABLE notification (
       SensorID  VARCHAR (30),
        Time      time without time zone,
        Date      date,
        Reason    VARCHAR (30),
        CONSTRAINT fk_1 FOREIGN KEY (sensorid,time,date) references time(sensorid,time,date)

);
By the way,I don't think it is a good idea to name columns as Time, Date etc..
Regards,
Jayadevan





DISCLAIMER:

"The information in this e-mail and any attachment is intended only for the person to whom it is addressed and may contain confidential and/or privileged material. If you have received this e-mail in error, kindly contact the sender and destroy all copies of the original communication. IBS makes no warranty, express or implied, nor guarantees the accuracy, adequacy or completeness of the information contained in this email or any attachment and is not liable for any errors, defects, omissions, viruses or for resultant loss or damage, if any, direct or indirect."