Re: - Mailing list pgsql-novice
> 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."
> 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."
pgsql-novice by date: