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
_________________________________________________________________
Send us your Hotmail stories and be featured in our newsletter
http://clk.atdmt.com/UKM/go/195013117/direct/01/

Search Discussions

  • Andreas Kretschmer at Feb 22, 2010 at 2:16 pm

    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
  • Jayadevan M at Feb 23, 2010 at 3:44 am

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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-novice @
categoriespostgresql
postedFeb 22, '10 at 2:06p
activeFeb 23, '10 at 3:44a
posts3
users3
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2022 Grokbase