外键，根据关系他们属于哪里 [英] Foreign keys, where they belong depending on relationships

问题描述

我已经在数据库中使用了一些公平的数据（MySQL，Oracle ），尽管通常只有DML是系统以前的工程。

I've worked with databases a fair bit (MySQL, Oracle) though typically only DML as the systems had been previously engineered.

我正在开发一个项目作为唯一的开发人员，负责应用程序开发和DBA;后者当然证明更有问题。

I'm working on a project as the sole developer, and am responsible for application development, and DBA; the latter of course proving more problematic.

无论如何 - 我需要刷新外键约束，以及在模型中的逻辑放置。给出以下内容：

In any case - I need a refresh on foreign key constraints, and their logical placement in a model. Given the following:

dbc_user
    .user_id INT, Primary Key, Auto Increment
    // arbitrary columns

dbc_user_profile
    .user_id INT, Primary Key
    // arbitrary columns

这些表是一个 1到1 关系。我明白，鉴于这种关系，他们的列可以合并成一个单独的表，但让我们分开。外键属于，然后在 dbc_user_profile 表中？

These tables are a 1-to-1 relationship. I understand that given that relationship, their columns could be consolidated into a single table, but let's just keep them separate. The foreign key belongs then, in the dbc_user_profile table?

many-to-many ？

dbc_user
    .user_id INT, Primary Key, Auto Increment

dbc_city
    .city_id INT, Primary Key, Auto Increment

dbc_user_city
    .city_id INT, Primary Key
    .user_id INT, Primary Key

哪些表是引用/引用的表？

Which tables are the referencing/referenced tables?

由于某些原因似乎找不到一个快速而肮脏的教程，这就解释了关系。

For some reason I can't seem to find a quick and dirty tutorial on this, that explains the relationships.

所以这两个部分的问题;

So two part question;

• 有人可以为半精通教程建议一个关于外键的DDL细节的教程吗？或者;


• 有人可以在 1到1 ， 1到m 和 m-to-m 关系和FK引用？

• Can someone suggest a tutorial for the semi-savvy that hammers out the details of DDL with regard to foreign keys? or;
• Can someone give a quick rundown on 1-to-1, 1-to-m, and m-to-m relationships and FK references?

推荐答案

在子表上声明并引用父表。

A foreign key is declared on the child table and references the parent table.

以1对1的关系，似乎 DBC_USER 是父级和 DBC_USER_PROFILE 是孩子。因此，您可以在 DBC_USER_PROFILE 表上声明外键，并引用 DBC_USER 表。 （Oracle语法）

In a 1-to-1 relationship, it would appear that DBC_USER is the parent and DBC_USER_PROFILE is the child. So you'd declare the foreign key on the DBC_USER_PROFILE table and reference the DBC_USER table. Something like (Oracle syntax)

CREATE TABLE dbc_user_profile (
  user_id int primary key,
  <<more columns>>
  constraint fk_user_profile_user_id foreign key (user_id)
    references dbc_user( user_id )
)

以1到m的关系，通常很清楚哪个表是父母，哪个是孩子。父母在小孩的m行中有1行。所以你在子表上声明外键并引用父表。

In a 1-to-m relationship, it generally pretty clear which table is the parent and which is the child. The parent has 1 row for m rows in the child. So you declare the foreign key on the child table and reference the parent table.

CREATE TABLE parent (
  parent_id int primary key,
  <<additional columns>>
);

CREATE TABLE child (
  child_id int primary key,
  parent_id int references parent( parent_id ),
  <<additional columns>>
);

对于m到m的关系，外键将在映射表上定义，引用两个父表。所以外键将在 DBC_USER_CITY 表上声明，并引用 DBC_USER 和 DBC_CITY 。像

For an m-to-m relationship, the foreign key would be defined on the mapping table and would reference the two parent tables. So the foreign keys would be declared on the DBC_USER_CITY table and would reference both DBC_USER and DBC_CITY. Something like

CREATE TABLE dbc_user_city (
  city_id int,
  user_id int,
  constraint pk_dbc_user_city primary key( city_id, user_id ),
  constraint fk_dbc_user_city_city_id foreign key( city_id )
    references dbc_city( city_id ),
  constraint fk_dbc_user_city_user_id foreign key( user_id )
    references dbc_user( user_id )
)

显然，语法往往是相对数据库特定的。特别是对于Oracle， PSOUG站点上有一个完整的语法选项列表。有关包含各种数据库语法的更一般性参考，可以使用 w3schools站点。

Obviously, the syntax tends to be relatively database specific. For Oracle in particular, there is a pretty complete list of the syntax options on the PSOUG site. For a more general reference that includes syntax for a variety of databases, you could use the w3schools site.

这篇关于外键，根据关系他们属于哪里的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！