多个表和列的外键？ [英] Foreign key for multiple tables and columns?

问题描述

我一直在学习外键，我想知道如何使用它的方式是正确的在下面的例子：

  CREATE TABLE user（
 id INT（11）NOT NULL AUTO_INCREMENT，
 username VARCHAR（50）NOT NULL，
 password VARCHAR（20）NOT NULL，
 PRIMARY KEY id）
）; 
 
 CREATE TABLE items（
 i_id INT（11）NOT NULL AUTO_INCREMENT，
 name TINYTEXT NOT NULL，
 price DECIMAL（8,2）NOT NULL，
 PRIMARY KEY（i_id）
）; 
 
 CREATE TABLE user_purchase（
 i_id INT（11）NOT NULL，
 name TINYTEXT NOT NULL，
 id INT（11）NOT NULL，
 FOREIGN KEY（i_id）REFERENCES items（i_id），
 FOREIGN KEY（name）REFERENCES items（name），
 FOREIGN KEY（id）REFERENCES user（id）
）; 
  

感谢

解决方案

您不必包含项目名称。这被称为反规范化解。
你应该只有在items表中，只引用id，然后如果你需要的名称，你也可以基于主键（id）加入它。
否则在我看来是完全正确的。

  CREATE TABLE用户（
 id INT NOT NULL AUTO_INCREMENT，
 username VARCHAR（50）NOT NULL，
 password VARCHAR（20）NOT NULL，
 PRIMARY KEY（id）
）; 
 
 CREATE TABLE items（
 i_id INT（11）NOT NULL AUTO_INCREMENT，
 name TINYTEXT NOT NULL，
 price DECIMAL（8,2）NOT NULL，
 PRIMARY KEY（i_id）
）; 
 
 CREATE TABLE user_purchase（
 i_id INT（11）NOT NULL，
 name TINYTEXT NOT NULL，
 id INT（11）NOT NULL，
 FOREIGN KEY（i_id）REFERENCES items（i_id），
 FOREIGN KEY（id）REFERENCES user（id）
）;有时候，当性能很关键时，你必须使用非规范化的表。它可以更快。
 
 
 
规范化对于避免不同的异常是很重要的。 
如果表具有高级正常形式，那么表将不会是冗余的，并且不会有这些异常。例如，如果您有某些存储在多个位置的内容，您必须注意保持所有冗余数据为最新。 
 
 
 
在有外键的情况下，可以帮助您保持数据完整性，但没有外键
 
 
 
这是一种异常现象。
 $ 
 
 
 
有很多种类型，最好避免它们。阅读更多这里关于异常 
 
 
 
在某些情况下，您必须denoramalize。所以存储一些数据冗余，因为性能问题。 
 
 
 
标准化的细节由不同的正式形式的主题所涵盖：
 NF0，NF1，NF2 ，NF3和BCNF 
 
 
 
 正式表格详细 
 
 
 
有关无法分解为更高正常形式的数学基础的更多详细信息，请参阅功能依赖性。这将帮助你了解为什么你可以保持ids冗余。实际上，它们是必需的冗余，因为您需要它们，以便能够以后重建原始数据集。这将是不同正常形式的定义。 
 
 
 
 功能相关性 
 
I have been learning about Foreign keys and I wanted to know if the way I used it is correct in the example below:
CREATE TABLE user(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  name TINYTEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  name TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
  FOREIGN KEY (i_id) REFERENCES items(i_id),
  FOREIGN KEY (name) REFERENCES items(name),
  FOREIGN KEY (id) REFERENCES user(id)
);
Thanks

Now, how can I get maximum information from just the Foreign Key if I use, let's say, PHP?
解决方案
You don't have to include item name in both tables. This is called a denormalized solution.
You should have it only in the items table and only refer to the id, then if you need the name also you can join it based on the primary key(id).
Otherwise it is totally OK in my opinion.
CREATE TABLE user(
  id INT(11) NOT NULL AUTO_INCREMENT,
  username VARCHAR(50) NOT NULL,
  password VARCHAR(20) NOT NULL,
  PRIMARY KEY (id)
);

CREATE TABLE items(
  i_id INT(11) NOT NULL AUTO_INCREMENT,
  name TINYTEXT NOT NULL,
  price DECIMAL(8,2) NOT NULL,
  PRIMARY KEY (i_id)
);

CREATE TABLE user_purchase(
  i_id INT(11) NOT NULL,
  name TINYTEXT NOT NULL,
  id INT(11) NOT NULL,
  FOREIGN KEY (i_id) REFERENCES items(i_id),
  FOREIGN KEY (id) REFERENCES user(id)
);
Sometimes when performance is critical you have to use denormalized tables. It can be much faster.

Normalization is important to avoid different anomalies.
If you have tables in a high level normal form then your tables won't be redundant and wont have these anomalies. For example if you have something stored in multiple locations you have to look after to keep all the redundant data up to date. This gives you a chance to do this incorrectly and end up having different anomalies.

In your situation having a foreign key helps you to keep data integrity but without a foreign key for the name you would be able to have items with names in the purchases that are not present in the items table.

This is a kind of anomaly.

There are many kinds of this, best to avoid them as long as you can.

Read more here about anomalies

In some cases you must denoramalize. So store some data redundantly because of performance issues. This way you can save some join operations that could consume much time.

Details of normalization are covered by topics of different normal forms:
NF0, NF1, NF2, NF3 and BCNF

Normal forms in detail

For further details about the mathematical foundations of loseless decomposition to higher normal forms see "Functional dependencies". This is going to help you understand why you can keep the ids "redundant". Virtually they are necessary redundancy, since you need them in order to be able to later rebuild the original dataset. This is going to be the definition for the different normal forms. What level of this redundancy is allowed?

Functional Dependencies

                        
这篇关于多个表和列的外键？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！