×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Indexing - Simple Problem

Indexing - Simple Problem

Indexing - Simple Problem

(OP)
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...

RE: Indexing - Simple Problem

(OP)
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..

RE: Indexing - Simple Problem

Quote (nikeloeleven):

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

Quote (nikeloeleven):

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)

RE: Indexing - Simple Problem

(OP)
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

RE: Indexing - Simple Problem

Quote (nikeloeleven):

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)

RE: Indexing - Simple Problem

If your database understands normal SQL statements, try this:

CODE

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close