将主键与另一个主键关联可以吗？ [英] Is it ok to have a primary key relate to another primary key?

问题描述

这是我的ERD的一部分：

< Orders表中的p> OrderID与Order Details表中的OrderID / ProductID主键相关。我不认为主键可以与其他主键关联，但是我不确定其他如何关联表。使它们成为主键和外键？

解决方案

您似乎缺少PK（主键）背后的一些基本概念，而并非唯一NULL，FK（外键），超键，CK（候选键），简单PK与复合PK / UNIQUE和ERD（实体关系图）。从最新答案：

PK和FK在某些方法和
产品中被错误地称为关系。应用程序关系由表表示。 （基于
表和查询结果。）PK& FK是约束条件：它们告诉
DBMS仅某些情况会出现，因此它可以通知您
何时犯某些错误。它们不是关系，它们是&每个数据库状态和应用程序情况。 您确实
不需要知道约束来更新和查询
数据库。

只需根据您的人际关系/表的真实情况声明：

• PK或UNIQUE NOT NULL声明表示列集中的每个子行值都是唯一的。即列集是一个超键。 （一个PK只是一个独特的UNIQUE NOT NULL。）


• FK声明说，引用列中的列列表子行值也必须位于引用的超键列中。

我不认为主键可以与其他主键相关，

它们可以：主键可以是引用另一个超键的FK。 （您似乎在使用相对于来表示被FK引用。）

但是请注意：这里有两个PK Order OrderID&在（相关）OrderLine中作为FK引用的产品ProductID。但是它们每个都是FK引用（与...相关？）OrderLine composite PK {OrderID，ProductID}的 part 。

，但我不确定如何关联表。

首先声明CKs（候选键）：不包含较小超级键的超级键。然后声明FK。 （然后对于SQL DBMS声明FK引用的任何未声明的超键。）

将它们都设置为主键还是外键？

是：它们是Order&产品。它们是OrderLine中引用Order&的FK。产品。而且OrderLine的PK恰好是{OrderID，ProductID}。

PS 在您的ERD风格中，这些行（显然）仅仅是FK。 ，所有实体和关系都有表格。在某些形式的ERD中，有实体表，带标签的线表示关系/表（每端都涉及FK）和未带标签的线仅表示FK。当您查看图表样式时，请务必确保您了解如何确定代表关系/表的图标和这些关系在应用程序中的含义。 （不只是他们的基数。）

Here's the part of my ERD:

OrderID from the Orders table relates to the OrderID/ProductID primary key in the Order Details table. I don't think primary keys can relate to other primary keys, but I'm not sure how else to relate the tables. Make them both primary and foreign keys?

解决方案

You seem to be missing some basic ideas behind PKs (primary keys), UNIQUE NOT NULL, FKs (foreign keys), superkeys, CKs (candidate keys), simple vs composite PKs/UNIQUEs and ERDs (Entity-Relationship Diagrams). From this recent answer:

PKs & FKs are erroneously called "relationships" in some methods and products. Application relationships are represented by tables. (Base tables and query results.) PKs & FKs are constraints: they tell the DBMS that only certain situations can arise, so it can notice when you make certain errors. They are not relationships, they are statements true in & of every database state and application situation. You do not need to know constraints to update and query a database.

Just declare per what is true of your relationships/tables:

• a PK or UNIQUE NOT NULL declaration says that every subrow value in a column set is unique. Ie that the column set is a superkey. (A PK is just a distinguished UNIQUE NOT NULL.)
• a FK declaration says that a column list subrow value in referencing columns must also be in referenced superkey columns.

I don't think primary keys can relate to other primary keys,

They can: A primary key can be a FK referencing another superkey. (You seem to be using "relates to" to mean "is referenced by a FK in").

But note: Here you have two PKs Order OrderID & Product ProductID referenced as FKs in ("relating to") OrderLine. But they are each FKs referencing ("relating from"?) part of OrderLine composite PK {OrderID,ProductID}.

but I'm not sure how else to relate the tables.

First declare CKs (candidate keys): Superkeys that don't contain smaller superkeys. Then declare FKs. (Then for a SQL DBMS declare any undeclared superkeys referenced by FKs.)

Make them both primary and foreign keys?

Yes: They are PKs in Order & Product. They are FKs in OrderLine referencing Order & Product. And the PK of OrderLine happens to be {OrderID,ProductID}.

PS In your style of ERD the lines are (apparently) merely FKs, with all the entities and relationships having tables. In some forms of ERDs there are entity tables, labeled lines representing relationships/tables (each end involving a FK) and unlabeled lines representing just FKs. When you see a diagram style always be sure you understand how to determine what icons represent relationships/tables and just what those relationships are in terms of the application. (Not just their cardinalities.)

这篇关于将主键与另一个主键关联可以吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！