Error 1005 in MySQL (from foreign key syntax?)

I'm getting an error while creating the following tables in MySQL.

Here's the error:

ERROR 1005 (HY000): Can't create table 'Works' (errno: 150)

Could you guys take a look at the foreign keys of Works() and let me know if you see anything weird about them that would cause this error to happen?

Here's my overall schema:

create table Employee(
        Lastname    varchar(10),
        FirstName   varchar(10),
        MidInitial  char(1),
        gender      char(1),
        street      varchar(10),
        city        varchar(10),
        primary key(Lastname, FirstName, MidInitial));

create table company(
    company_name    varchar(20),
    city    varchar(10),
    primary key(company_name));

create table Works(
    Lastname    varchar(10),
    FirstName   varchar(10),
    MidInitial  char(1),
    company_name    varchar(20),
    salary      numeric(8,2),
    primary key(Lastname, FirstName, MidInitial, company_name),
    foreign key(Lastname, FirstName, MidInitial) references Employee,
    foreign key(company_name) references company);

Thanks so much!

回答1:

Your errors are caused because of incorrect foreign key syntax.

However, I think you should use ID fields instead of doing string composite primary keys. There are a few issues with your method...

It will make it harder for the DB to join tables together compared to using an integer (ID) field to join (harder == more processing time).
It is valid to have multiple people with the same name, even using a middle initial.
What happens if you have to change someone's name? Either because it was stored wrong or they got married or anything... That means you'll have to not only update your employee table, but the works table and any other table you've used the name as a foreign key.

Take a look at this: http://sqlfiddle.com/#!2/2dc8c/3/0

I've added a table ID to each table. It is an unsigned int, which means it cannot be negative (because that wouldn't make much sense). It is also auto_increment, which means that each time you add a row to the table, this ID will be auto generated and go up by 1.

    create table Employee (
          Employee_ID int unsigned auto_increment primary key,

          Lastname    varchar(10),
          FirstName   varchar(10),
          MidInitial  char(1),

          gender      char(1),

          street      varchar(10),
          city        varchar(10),

          unique (Lastname, FirstName, MidInitial)
      );

You would add things to this table like this:

    insert into Employee (Employee_ID, LastName, FirstName, MidInitial) 
                   values (null,       'Smith',  'John',    'K');

The null will become the automatically generated ID. It will be unique for each row.

Also, a unique constraint means that the combination of these fields must be unique in the table. However, with a big enough company, I bet two people will have the same name. In real life, i would suggest removing this unique constraint.

I made similar changes to the company table...

     create table company(
         company_ID      int unsigned auto_increment primary key,

         company_name    varchar(20),
         city            varchar(10),

         unique (company_name)
    );

Things could be added like:

     insert into company values (null, 'Taco Bell', 'Paris');

So... for works.... instead of storing the full name of each person and the full company name over and over again in this table, now we only have to store the ID's.

    create table Works (
         works_id      int unsigned auto_increment primary key,

         employee_id   int unsigned, 
         compay_id     int unsigned,  

         salary        numeric(8,2), 

         foreign key (employee_id) references Employee (employee_id), 
         foreign key (compay_id) references company (company_id) 
    );

You could add things to works like this:

    insert into Works values (null, 1, 1, '10.00');

Since John Smith was our first employee, his Employee_ID would be 1. To verify that, just try select * from Employee where FirstName='John' and LastName='Smith'. Taco Bell would also get company_id = 1. By inserting those values into works, that means John now works at Taco Bell.

I would also suggest you add fields like start_date and end_date and job_title to your works table. And you would want to pay special consideration to any unique constraints for this table, too. People can work for the same company more than once. They can also have different jobs.

When you want to get your data back out, you would use a query like this:

  select FirstName, MidInitial, LastName, 
         Company_Name, 
         Salary 

  from   employee

         join works 
           on works.employee_id = employee.employee_id

         join company 
           on works.company_id = company.company_id

which is just a fancy way of saying this:

  select FirstName, MidInitial, LastName, 
         Company_Name, 
         Salary 

  from   employee, works, company

  where  employee.employee_id = works.employee_id and

         company.company_id = works.company_id

Some notes on database things...

Choose a naming convention and stick to it! If you want to use CamelCase, use it everywhere. If you_want_to_use underscores in your names, use them everywhere. There are tons of naming conventions to pick from: prefixing attributes (columns/fields) with the table name, using common abbreviations (or not), where capitalization is used (or not)... this mostly comes down to personal preference but there are articles out there about the pros and cons about using certain ones. Last note, _just because you can use spaces in a name,__ doesn't mean you should. `Almost all` databases let [you use spaces] in names if you want but it can cause a lot of issues later.
Table names should not be plural. This is a pet-peeve of mine and here's why: We know a table will hold many records... many people/persons, many employees, multiple companies, multiple entries of whatever type or kind. Each row describes just one of these things. Sometimes it just doesn't even make sense to have the name be plural. Other times, it is questionable - like the works table. If you sometimes make it plural, and sometimes make it singular, it can get confusing later. By just making it all singular, it still makes perfect sense, and you aren't switching back and forth or having to look up the exact name when writing queries.
Datatypes are important and try to be consistent across tables for similar fields (like all ids the same type; make all boolean fields all bits or integers or whatever, just make them the same). There are different sizes of integer types you can choose from. Think about size, performance and what is appropriate for your needs. Decide if you really need a nvarchar or if a varchar is okay.
- Dates should never be stored as a string. Use the appropriate date, datetime, time, or timestamp datatype. This will help you so much later on when you need to retrieve it, compare it or use it in calculations. Another important decision is how you chose to handle timezones. I like to store everything in UTC and handle any timezone offset things on the front end, when the info is presented to the user. This keeps everything consistent and I don't have to worry about if the row was inserted at 6pm based on my user's computer time, the user's browser time, my database's time, or the server's time.
Include an ID field that is unique for the row in every table. The easiest way to do this is to use an auto_increment (mysql) or identity(1,1) (sql server) field so the database keeps track of it for you. These values can be reset or reseeded if you need it.
Learn to use normalization.
Learn what transactions do and why they are important... even if you don't use them.
Learn about the different kind of joins. This is one of the best explanations I have ever seen. The main thing to pay attention to is if the join should be an outer or inner join.
Learn about SQL Injection and more importantly, how to prevent it (that link is for PHP).
If you're using PHP, don't use the old mysql_ class. Instead, use PDO or MySQLi_. Info...
The big thing about databases is data integrity, validation and sanitization. Users will want to put all kinds of sloppy, dirty data into your tables. Is it MO or Missouri? Female, F, woman, girl, or yes? Is the salary 15.00 an hour, 50k annually, or 3,000 a paycheck? Is it 12/31/2013, 31/12/2013, 12-31-13, Dec 31, 2013 or december thirty-fist two thousand and thirteen?
Decide if you want to allow NULL or not. It makes things more complicated because of triple state logic and you will need to check for it later. Some people decide to just use an empty string instead. NULL is more of a state than an actual value and it means undefined or unknown - the value could be anything. I use null because an empty string is sometimes a valid value for a field. For example, setting Middle_Initial to equal an empty string could mean the person doesn't have a middle initial or it could mean you just don't know what it is. To me, these things are different. To other people, the difference doesn't matter. Just consider numbers... is 0 the sames as unknown?
If nothing else, just be consistent.

回答2:

Appart from having the columns NULLABLE for your PRIMARY KEY, the error you are getting is not about that, but about the multiple columns on your FOREIGN KEY and missing the columns that are referenced in the original tables. You should do:

CREATE TABLE Works (
  Lastname VARCHAR(10) NOT NULL,
  FirstName VARCHAR(10) NOT NULL,
  MidInitial CHAR(1) NOT NULL,
  company_name VARCHAR(20) NOT NULL,
  salary NUMERIC(8, 2),
  PRIMARY KEY (Lastname,FirstName,MidInitial,company_name),
  CONSTRAINT fk_works FOREIGN KEY (Lastname,FirstName,MidInitial) 
      REFERENCES Employee(Lastname, FirstName, MidInitial),
  FOREIGN KEY (company_name) REFERENCES company(company_name)
  );

回答3:

The primary key column cannot be NULL, you need to set it to NOT NULL (not allowing null values), something like this

create table Employee(
        Lastname    varchar(10) NOT NULL,
        FirstName   varchar(10) NOT NULL,
        MidInitial  char(1) NOT NULL,
        gender      char(1),
        street      varchar(10),
        city        varchar(10),
        primary key(Lastname, FirstName, MidInitial));

create table company(
    company_name    varchar(20) NOT NULL,
    city    varchar(10),
    primary key(company_name));

This will resolve the issue but its a bad practice to have Primary Key on varchar values, I think you should add an Identity column in each table to use as a primary key, and create check constraints on other columns to enforce your business rules .