Home » SQL & PL/SQL » SQL & PL/SQL » wht would be my primary key
wht would be my primary key [message #192340] Mon, 11 September 2006 23:48 Go to next message
marsudhir
Messages: 2
Registered: September 2006
Junior Member
Hi
I would like to create a table which stores the daily reading of my power consumption in a commercial complex. suppose u have 3 occupants and 3 different meters installed for them.
my table structure would be

cust_id number
cust_name varchar
meter_reading number
reading_date date

my questions are
1.in this table wht could be my primary key,
2. how to maintain the daily record every customer

with regards
sudhir
Re: wht would be my primary key [message #192341 is a reply to message #192340] Mon, 11 September 2006 23:54 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

ur primary key should be the customer_id column
Re: wht would be my primary key [message #192342 is a reply to message #192340] Tue, 12 September 2006 00:00 Go to previous messageGo to next message
sunsanvin
Messages: 60
Registered: April 2006
Location: Hyderabad
Member

and you have to update the table with a where condition refers the primary key like this statement

update my table set ............all the rest of columns where cust_idnumber= 1 Cool
Re: wht would be my primary key [message #192343 is a reply to message #192340] Tue, 12 September 2006 00:03 Go to previous messageGo to next message
Liza79
Messages: 74
Registered: September 2006
Member
The ideal situation is: that you should have two tables for this

One for Customers.
One for Their Meter Readings.

Customer Table:
CREATE TABLE CUSTOMER
(CUST_ID NUMBER,
CUSTOMER_NAME VARCHAR2(30),
CONSTRAINT CUST_PK PRIMARY KEY (CUST_ID));


Meter Table:

CREATE TABLE METERS
(METER_ID NUMBER,
METER_READING NUMBER,
READING_DATE DATE,
CUST_ID NUMBER,
CONSTRAINT METER_PK PRIMARY KEY (METER_ID),
CONSTRAINT METER_CUSTOMER_FK FOREIGN KEY (CUST_ID)
REFERENCES CUSTOMER (CUST_ID));


But if you really want them to be in the same table as you already described in your question. You will probably need to put the primary key on the CUST_ID and READING_DATE columns. So that you should not get the meter readings for the same customer in one day. But this structure is going to create a lot of DML Anomalies for you later.

Liza.
Re: wht would be my primary key [message #192351 is a reply to message #192343] Tue, 12 September 2006 00:36 Go to previous message
marsudhir
Messages: 2
Registered: September 2006
Junior Member
thank u liza
Previous Topic: ROWS to COLS
Next Topic: IN List
Goto Forum:
  


Current Time: Mon Dec 05 18:54:46 CST 2016

Total time taken to generate the page: 0.07743 seconds