Home » SQL & PL/SQL » SQL & PL/SQL » primary key
primary key [message #36520] Sun, 02 December 2001 23:34 Go to next message
Eva
Messages: 16
Registered: July 2001
Junior Member
Hello.
I have the following question:
I want to create a table (used by a tv-channel) that will record the costs of
commercials(the cost will vary according to the duraration
of the commercial(duration) and the period that the commercial will be braodcasted ( the period
starting from Valid_from and ending at Valid_to)
I don't know if it is essential to use the 'primary key'....
What is the difference between Version 1 and version2;
Version 1

create table spot_cost
(Show_name varchar2(20) NOT NULL,
Duration varchar2(6),
Cost integer,
Valid_From DATE,
Valid_To DATE
);

Version2
create table spot_cost
(Show_name varchar2(20) NOT NULL,
Duration varchar2(6),
Cost integer,
Valid_From DATE,
Valid_To DATE,
primary key(Show_name,Duration,Cost,Valid_From));

Thank a lot, your help will be valuable
Eva

----------------------------------------------------------------------
Re: primary key [message #36544 is a reply to message #36520] Tue, 04 December 2001 05:22 Go to previous message
tinel
Messages: 42
Registered: November 2001
Member
You should use a primary key, it's quite useful, but it's not a good ideea to use a primary key like you define in version 2, you will not be able to have in a show two commercials with the same duration and the same cost and starting date, i think this may be possible in a tv-channel.
You could create tables like this:
CREATE TABLE shows(
show_id NUMBER(3),
show_name VARCHAR2(100),
constraint Shows_PK primary key (show_id));

CREATE TABLE spot_cost(
Spot_Id NUMBER(3),
Show_Id NUMBER(3),
Duration VARCHAR2(6),
Cost NUMBER(10,2),
Valid_From DATE,
Valid_To DATE,
CONSTRAINT Spot_PK PRIMARY KEY (Spot_Id),
CONSTRAINT Spot_FK FOREIGN KEY (Show_Id) REFERENCES Shows (Show_Id));

this has the advantage that you will not be able to insert a comercial for a show that dosen't exist, you car read more about this at: http://technet.oracle.com/doc/server.815/a68003/toc.htm
Bye

----------------------------------------------------------------------
Previous Topic: Problems with parmeter in procedures
Next Topic: Re: Problems with parmeter in procedures
Goto Forum:
  


Current Time: Thu Mar 28 19:04:51 CDT 2024