Re: please check code... simple SQL

[Rick James]

> CHAR(25)
Use VARCHAR unless the strings are really fixed length!

Do you ever need to do something like
SELECT ... FROM Students where firstName = 'Bhavin';
If so, you need
INDEX(firstName)

So, in general, let's see all the types of queries you will perform; then we can judge the indexes.

Don't ask us to check your code; ask MySQL to check it!...

mysql> create database f519159;
Query OK, 1 row affected (0.12 sec)

mysql> use f519159;
Database changed
mysql> CREATE TABLE Student
    -> (studentId CHAR(10), firstName CHAR(15), degreeScheme CHAR(30), PRIMARY KEY (studentId));
Query OK, 0 rows affected (0.76 sec)

mysql> CREATE TABLE Staff
    -> (staffId CHAR(10), staffName CHAR(15), staffGrade CHAR(25), PRIMARY KEY (staffId));
Query OK, 0 rows affected (0.48 sec)

mysql> CREATE TABLE ModuleT
    -> (moduleId CHAR(10), moduleName CHAR(50), credits CHAR(2), PRIMARY KEY (moduleId));
Query OK, 0 rows affected (0.28 sec)

mysql> CREATE TABLE Registered
    -> (studentId CHAR(10), moduleId CHAR(10), PRIMARY KEY (studentId, moduleId),
    -> FOREIGN KEY (studentId) REFERENCES Student(studentId),
    -> FOREIGN KEY (moduleId) REFERENCES Module(moduleId));
ERROR 1005 (HY000): Can't create table 'f519159.registered' (errno: 150)

(and things got worse after that)

When defining FOREIGN KEYs, you should CREATE the tables in the right order.

You did not specify ENGINE=InnoDB, which you should do if you are using FOREIGN KEYs.