creating tables in mysql database

Question

i am trying to create two tables in mysql database via code

this is my creation script

CREATE DATABASE if not exists feedback;

CREATE TABLE IF NOT EXISTS Authorizations(
    authDesc CHAR(50),
    id MEDIUMINT NOT NULL AUTO_INCREMENT,
    authLevel INT NOT NULL DEFAULT 0,

    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS Users(
    lastName CHAR(50) NOT NULL,
    userID MEDIUMINT NOT NULL,
    phone CHAR(15),
    email CHAR(50),
    address CHAR(100),
    authLevel INT NOT NULL DEFAULT 0,

    FOREIGN KEY(authLevel) REFERENCES Authorizations (authLevel),
    login CHAR(30) NOT NULL,
    firstName CHAR(50) NOT NULL,

    PRIMARY KEY (login),password CHAR(30) NOT NULL
);

i am getting this error when trying to execute that

java.sql.SQLException: Can't create table 'feedback.users' (errno: 150)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1078)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4120)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:4052)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2503)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2664)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2809)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1811)
    at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1725)
    at com.database.Database.createDatabase(Database.java:45)
    at com.database.Main.main(Main.java:6)

line 45 has the create table users line in my code

my database engine is InnoDB, via mysql

what am i doing wrong ?

Answer 1

Your problem

Code wise

You can only create a FOREIGN KEY constraint by referencing a column or set of columns which uniquely identify one single row in your destination table.

That means that @Ulrich was right you need an index but not any index you need an UNIQUE KEY constraint.

Logical

You need to read up on a few things. A direct solution like "add an index here" might silence the error but it may also restrict what you can do with those tales (due to the uniqueness constraint).

Your table structure may be the problem, and you may need to fully understand UNIQUE KEY an FOREIGN KEY before you can take a new approach to the table structure.

MySQL background info about keys and indexes

Indexes

When creating an indexed column the MySQL server stores some data about the content of that column which makes it easier for the server to find records which are filtered/sorted using that column. So indexes represent a data structure stored separately by MySQL for easier reading.

If you know your application will attempt to search for records by a certain column (ex.: title, name, created_at, etc.) you may want to declare a simple INDEX or KEY to specify that you want easy lookup for those columns. Direct selects will be faster, joins will be faster etc..

The table will also use more memory because some extra data is stored somewhere regarding those columns.

Also you might need to declare a multiple column INDEX in order to cover for ex. the title and the content of a blog article. INDEXES have limits to the amount of (string length) of data they can use so you'll sometimes have to declare an INDEX which uses just the first 512 (or some number) characters of the column(s).

Constraints

When creating a constraint (a PRIMARY KEY, a UNIQUE KEY or a FOREIGN KEY) the constraint needs to look up data in the table in order to enforce the rule (uniqueness or referenceability etc.).

Because of that, these constraints will also need an INDEX. Every time you declare a column as PRIMARY KEY or as UNIQUE KEY an INDEX is also created, so you don't have to create it yourself.

When declaring a FOREIGN KEY the index that needs to be created should be in the referenced table not in the current table. Because of that you have to create it yourself.

Answer 2

You cannot reference with a foreign key a field of an other table that is not primary key.

Use instead Authorization.id as a field to link to your foreign key.

Then if you really need to know about authLevel in users table, join the two tables.