| primary key [message #36520] |
Sun, 02 December 2001 23:34  |
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  |
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
----------------------------------------------------------------------
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
