SQL Errors With Interbase

[nikeloeleven]

Hi I'm using Interbase to create a multi-user database.
I'm fairly new to Interbase and SQL.
I was creating a database as shown below:


Create Database 'c:\IPMnterBaseBackbone.Gdb' user 'SYSDBA' Password 'masterkey';
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),
);

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),
);



When I try to execute it, I get this error:

Dynamic SQL Error
SQL error code = -104
Token unknown - line 15, char 1
)
Statement: 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),
)

I really can't figure out what I've done wrong here... Please help me.... Thanks...

 

[SQLSister]

Well at a guess I would say you can't create the foreign key relationship until the table it references is created.

Questions about posting. See faq183-874
Click here to learn Ways to help with Tsunami Relief

 

[nikeloeleven]

Ok ive rearragned the tables in such a way that all foreign key relations are made after the tables it references are created:


Create Database 'c:\IPMBackbone.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),
);








however, i get this error mesage now...



Dynamic SQL Error
SQL error code = -104
Token unknown - line 7, char 1
)
Statement: 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),
)

what shall i do now?

 

[nikeloeleven]

ive got rid of that problem by removing the comma at the end of the primary key definition... but now it's refusing to recognise the Primary Keys

eurgh

 

[nikeloeleven]

sorry i meant its refusin to recognise the "foreign keys" now