MySQL中的错误1005（来自外键语法？） [英] Error 1005 in MySQL (from foreign key syntax?)
问题描述
以下是错误：
错误1005（HY000）：Can not create table'Works'（errno：150）
你们可以看看Works（）的外键，并且让我知道你是否看到任何会导致这个错误发生的奇怪的事情？
以下是我的总体模式：
$ p $ create table Employee（
Lastname varchar（10），$ （1），
性别字符（1），
街道varchar（10），
city varchar（10），
$ FirstName varchar（10），
MidInitial char b $ b主键（Lastname，FirstName，MidInitial））;
create table company（
company_name varchar（20），$ b $ city varchar（10），
primary key（company_name））;
创建表Works（
Lastname varchar（10），
FirstName varchar（10），
MidInitial char（1），
company_name varchar（20 ），
薪水数字（8,2），
主键（姓氏，名字，MidInitial，company_name），
外键（姓氏，名字，MidInitial）引用Employee，
外键（company_name）引用company）;
非常感谢！
您的错误是由于不正确的外键语法造成的。
然而，我认为你应该使用ID字段而不是做字符串组合主键。你的方法有几个问题...
$ ul
这将使数据库难以连接在一起，使用一个整数（ID）字段加入（更难==更多的处理时间）。
即使使用中间名，也可以有多个名字相同的人。
员工表，而且工作已经使用这个名字作为外键。 http://sqlfiddle.com/#!2/2dc8c/3/0 我为每个表格添加了表格ID 。它是一个 unsigned int ，这意味着它不能是负数（因为这没有多大意义）。它也是 auto_increment ，这意味着每当你添加一行到表中时，这个ID将被自动生成并且上升1次。
create table Employee（
Employee_ID int unsigned auto_increment主键，
姓氏varchar（10），
FirstName varchar（10），
MidInitial char（1），
性别char（1），
street varchar（10），
city varchar （10），
unique（Lastname，FirstName，MidInitial）
）;
您可以像这样添加内容到这张表：
$ b $插入到Employee（Employee_ID，LastName，FirstName，MidInitial）
values（null，'Smith'，'John'，'K'）; b
此外，唯一约束表示这些字段的组合在表中必须是唯一的。但是，一个足够大的公司，我打赌两个人会有同样的名字。在现实生活中，我建议删除这个独特的约束。
我对公司表进行了类似的更改...
< （$）$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $
unique（company_name）
）;
可以添加如下内容：
<$ p插入到公司值（空，塔可钟，巴黎）; $ p>
所以... for works ....而不是将这个表中的每个人的全名和整个公司名称一遍又一遍地存储起来，现在我们只需要存储这个ID。
create table Works（
works_id int unsigned auto_increment主键，
employee_id int unsigned ，
compay_id int unsigned，
工资数字（8,2），
$ b外键（employee_id）引用Employee（employee_id），
外键（compay_id）引用company（company_id）
）;
您可以将这些内容添加到 works ：
插入Works值（null，1，1，'10 .00'）;
由于John Smith是我们的第一个员工，他的Employee_ID将是1.要验证这一点， code> select * from Employee where FirstName ='John'and LastName ='Smith'。塔可钟还将得到company_id = 1。通过将这些值插入到 works 中，这意味着John现在在Taco Bell工作。
我也建议你添加字段，如 start_date 和 end_date 和 job_title 到您的作品表中。而且你也要特别考虑这个表的任何独特的限制。人们可以不止一次地为同一家公司工作。他们也可以有不同的工作。
当您想要取回数据时，您可以使用这样的查询：
c $ c> id 是相同的类型;使所有的布尔型字段都是位或整型或其他，只要使它们相同）。您可以选择不同大小的整数类型。考虑大小，性能和适合您的需求。决定你是否真的需要一个nvarchar，或者如果一个varchar是好的。选择名字，MidInitial，姓氏，
公司名称，
工资
来自员工
加入作品
在works.employee_id = employee.employee_id
加入公司
在works.company_id = company.company_id
这只是一个奇妙的说法：
选择名字，MidInitial，姓氏，
公司名称，
工资
从员工，工作，公司
其中employee.employee_id =工作。 employee_id和
company.company_id = works.company_id
有关数据库的一些注意事项...
- 许多记录...许多人/人，许多雇员，多个公司，任何类型或种类的多个条目。每行只描述这些东西中的一个。有时候，名字是复数就没有意义了。其他时候，这是有问题的 - 就像
选择一个命名约定并坚持下去！如果你想使用
CamelCase，可以在任何地方使用。如果you_want_to_use在你的名字中有下划线，那么到处使用它们。有大量的命名约定可以选择：为表名添加属性（列/字段），使用常用缩写（或不使用大写字母）（大概是否使用）...这主要归结为个人偏好，但是是关于使用某些利弊的文章。最后一点，_just因为你可以在名称中使用空格__并不意味着你应该。几乎所有的数据库都会在名称中使用[你使用空格]，但如果你愿意的话，会导致很多问题后来。works表一样。如果你有时使用复数形式，有时使它成为单数形式，那么以后会变得混乱。通过将它们全部用单数来表示，它仍然是非常有意义的，而且在写查询时不会来回切换，也不必查找确切的名称。
- 日期应该永远不会被存储为字符串。 使用适当的日期，日期时间，时间或时间戳记数据类型。当您需要检索，比较或计算时，这将对您有所帮助。另一个重要的决定是你如何选择处理时区。我喜欢用UTC存储所有东西，并在信息呈现给用户时处理前端的任何时区偏移量。这保证了所有内容的一致性，而且我不必担心在下午6点这行是根据用户的计算机时间，用户的浏览器时间，数据库的时间还是服务器的时间插入的。
/>
包含 ID 对于每个表格中的行都是独一无二的。最简单的方法是使用 auto_increment （mysql）或 identity（1,1）（sql server ）字段，因此数据库会为您保存跟踪。这些值可以重置或重新调整，如果你需要它。学习使用 > 标准化。
mysql _ 类。 而是使用 PDO 或 MySQLi _ 。 信息... 数据库的重要之处在于数据完整性，验证和清理。用户会希望将各种潦草，肮脏的数据放到你的表中。是MO还是密苏里？女，女，女，还是是？薪水是每小时15.00，每年5万，还是3000？是2013年12月31日，2013年12月31日，2013年12月31日12时31分至13日，还是12月30日，确定您是否允许
NULL 或者不允许 由于三态逻辑，它使事情变得更加复杂，您将需要稍后检查。有些人决定只是使用空字符串。 NULL 是比实际值更多的状态，它意味着未定义或未知 - 值可以是任何值。我使用 null ，因为空字符串有时是字段的有效值。例如，设置 Middle_Initial 等于一个空字符串可能意味着这个人没有中间的首字母，也可能意味着你只是不知道它是什么。对我来说，这些事情是不一样的。对于其他人来说，差异并不重要。只要考虑数字...与未知的相同？ b $ bI'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!
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
employeetable, but theworkstable 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. Ifyou_want_to_useunderscores 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
workstable. 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.
- 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
IDfield that is unique for the row in every table. The easiest way to do this is to use anauto_increment(mysql) oridentity(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, usePDOorMySQLi_. 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
NULLor 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, settingMiddle_Initialto 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.
这篇关于MySQL中的错误1005（来自外键语法？）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
