Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Indexing - Simple Problem

Status
Not open for further replies.
nikeloeleven

nikeloeleven

Programmer
Feb 15, 2005
32
0
0
GB
Hi there. I'm new to Sql as you may know from my previous threads...I've been trying to Index a few of my tables.

I've searched everywhere but I havent been able to find out how to create an index on a table with more than one primary key i.e. joint primary keys.

Here is my small database structure:

Create Database 'c:\IPMBackbnone.Gdb' user 'SYSDBA' Password 'masterkey';

Create Table StaffMember
(StaffID VarChar(15) Not Null,
Forename VarChar(10) Not Null,
Surname VarChar(10) Not Null,
StaffGroup VarChar(10) Not Null,
Primary Key (StaffID)
);

Create Table FormTutor
(StaffID VarChar(15) Not Null,
Form VarChar(3) Not Null,
Primary Key (StaffID, Form),
Foreign Key (StaffID) References StaffMember(StaffID)
);

Create Table Pupil
(AdmissionNumber VarChar(4) Not Null,
Forename VarChar(10) Not Null,
Surname VarChar(10) Not Null,
DateofBirth VarChar(3) Not Null,
Form VarChar(3) Not Null,
Primary Key (AdmissionNumber),
Foreign Key (Form) References FormTutor(Form)
);

Create Table SubjectTarget
(AdmissionNumber VarChar(4) Not Null,
TargetNumber VarChar(15) Not Null,
TargetCategory VarChar(10) Not Null,
SubjectName VarChar(15) ,
Description VarChar(10) Not Null,
StrategyUsed VarChar(15) Not Null,
ExpectedEvidence VarChar(10) Not Null,
ReviewsMade VarChar(15) Not Null,
DateofLastReview VarChar(10) Not Null,
StatusofTarget VarChar(15) Not Null,
Evidence VarChar(10) Not Null,
Primary Key (AdmissionNumber, TargetNumber),
Foreign Key (AdmissionNumber) References Pupil(AdmissionNumber)
);

Create Table Subject
(SubjectName VarChar(15) Not Null,
StaffID VarChar(15) Not Null,
Primary Key (SubjectName),
Foreign Key (StaffID) References StaffMember(StaffID)
);


/* INDEXES FOR PRIMARY KEYS */
Create Unique Index StaffIDIndex On StaffMember(StaffID);





So far I've only created an Index on the StaffMember field. I want to create indexes on the other tables but some of them have more than one field as the primary key so I dont know how to index the tables with joint primary keys. Please can you help me on how to index them.

Some of the joint primary keys are also foreign keys so do i have "know anything specific" when i want to index joint keys where one or more of the fields are foreign keys?

Thanks a lot for ur help... I really appreciate it...
 
Sort by date Sort by votes
thnx r937

so for the staffmember table... wouldnt i need to do this:

Create Unique Index BookIDIndex On Book(BookIdNo);

if not.. then why write indexes on primary keys in the first place???>

and by the way... i'm using INTERBASE to do all this... so does the SQL version there automatically do indexes on primary keys.. if not then please help me out in the original question..
 
Upvote 0 Downvote
nikeloeleven said:
so for the staffmember table... wouldnt i need to do this:

Create Unique Index BookIDIndex On Book(BookIdNo);
Click to expand...
no, for the staffmember table you would not want to index the book table, which you apparently don't even have :)

nikeloeleven said:
if not.. then why write indexes on primary keys in the first place???
Click to expand...
my point exactly, primary keys get unique indexes, so you don't have to declare a unique index for them, it would be redundant


i cannot swear that this is true for interbase because i've never used it, but i would be extremely surprised if it didn't also do this, like all other databases that i know of

you see, a primary key must be unique, and the fastest way that the database engine has of ensuring that a new value doesn't already exist is to look it up in a unique index


rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)
 
Upvote 0 Downvote
Apparently SQL in Interbase don't automatically index the primary keys, I think.
So that means I HAVE to write the unique indexes for the primary keys.

How would i Write a unique index on a joint primary key field such as the "FormTutor" table please? It has a joint primary key of "StaffID and Form"....

thanks again
 
Upvote 0 Downvote
nikeloeleven said:
Apparently SQL in Interbase don't automatically index the primary keys, I think
Click to expand...
can you do an EXPLAIN in interbase? if so, do it on a query where you pull one row out of the table by its primary key value


if the EXPLAIN shows you that it does a table scan (which i would find exceedingly unusual), then i guess you're right

but if you do wish to create an index on the primary key, if interbase syntax follows the sql standard, it will be like this --

create table FormTutor
( StaffID varchar(15) not null
, Form varchar(3) not null
, primary key (StaffID, Form)
, constraint pk2 unique (StaffID, Form)
, foreign key (StaffID) references StaffMember(StaffID)
)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

dwf1962
  • Locked
  • Question
Trying to write a simple Foxpro prog to convert database of probabilities to a life expectancy table
Replies
1
Views
161
SkipVought
S
andy_sem
  • Locked
  • Question
.mdb database problem
Replies
2
Views
107
mmmacoss
mmmacoss
sam4help
  • Locked
  • Question
Help MS SQL Indexing: Table structure & Search criteria provided here
Replies
9
Views
101
sam4help
sam4help
mrcamo
  • Locked
  • Question
Moddeling problem
Replies
2
Views
51
mrcamo
mrcamo
globos
  • Locked
  • Question
Mixed columns design problem
Replies
13
Views
95
ceh4702
ceh4702

Part and Inventory Search

Sponsor

Back
Top