Indexing - Simple Problem

[nikeloeleven]

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...

 

[r937]

you do not need to create an index on a primary key, it already gets a unique index by default

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[nikeloeleven]

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..

 

[r937]

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

Create Unique Index BookIDIndex On Book(BookIdNo);

no, for the staffmember table you would not want to index the book table, which you apparently don't even have

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

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)

 

[nikeloeleven]

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

 

[r937]

Apparently SQL in Interbase don't automatically index the primary keys, I think

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)

 

[ChrisHunt]

If your database understands normal SQL statements, try this:

CREATE UNIQUE INDEX FormTutor_pk ON FormTutor (StaffID,Form);

I'd be really surprised if this wasn't being done automatically though.

-- Chris Hunt
Webmaster & Tragedian
Extra Connections Ltd

 

[willir]

Discussed here too, using VBA...
Strange Question About Boolean Data Types In Sql...