同一张表中的多个外键 [英] Multiple foreign keys from the same table

问题描述

我正在建立毕业设计管理系统的数据库。学生分为小组。有小组表和教师表。每个小组都有一名顾问和两名审查员。我在这里很困惑。我应该从教职员工表中创建3个FK吗？

这是SQL代码：

 创建表组
（
 groupID NUMBER不为空，
 nbStudents NUMBER不为空，
 avgGPA DOUBLE NOT NULL，
 projectName varchar（50）不为空， 
顾问ID号码
审查员1ID号码
审查员2ID号码
主键（groupID）
）； 
创建表教师
（
名称varchar（30）不为空，
 facultyID NUMBER（10）不为空，
电子邮件varchar（30）不为空，
 mobile NUMBER（15）不为null，
类型varchar 
主键（facultyID）
）； 
 
变更表组
添加约束FK_EX1外键（examiner1ID）
引用faculty（facultyID）; 
更改表组
添加约束FK_EX1外键（examiner2ID）
引用faculty（facultyID）; 
更改表组
添加约束FK_EX1外键（advisorID）
引用faculty（facultyID）; 
  

解决方案

编辑中...请参阅我的第一条评论。

只需在找到外键时说出它们即可。

外键说表中某列中的值必须显示为另一张（可能是同一张）表中某列的值，其中相应的列构成一个键。因此，在给定的设计中，只需在找到FK时声明它们即可。

尽管这些并不是真正的FK。首先，在SQL中，FK声明实际上声明了外键。其次，因为这些列可以为NULL。 SQL说明了如何检查它们，并且不检查列何时为NULL，这就是您想要的。但是该约束不是外键（超级）。我们只是在SQL数据库中称呼它。

查找描述您的应用程序情况的语句，然后进行规范化。

它不是非标准化的，本身具有多个列。这是一个常见的误解。

但是通常禁止使用至少是一个理想的设计。只需为需要针对某种情况说的每件事找到一个由列名参数化的参数化语句。每个语句都会得到一个表。

  //组[groupID]包含[nbStudents]个学生.....并具有顾问[advisorID ]和... 
 groups（groupID，nbStudents，...，advisorID，examinerID）
  

使语句为真的行进入表。查找描述应用程序情况所需的所有语句。

查找简单的语句并在以后重新排列为NULL。 $ p
$ b

请注意，以上语句仅适用于不包含NULL的行。但是您有时会想说，没有老师在这些角色中。

理想情况下，您只是想要

  //组[groupID]包含[nbStudents]个学生... [projectName]）
 groups（groupID，nbStudents，...，projectName）
 // [facultyID]咨询（groupID）
咨询（facultyID，groupID）
 // // [facultyID]检查[groupID] 
检查（faculty，groupID）
  

受每组教师人数的限制。如果您正确地编写一个没有null的关系设计，然后进行规范化，您将得到这种简单的东西。不用担心语句/表的数量。它们只是反映了应用程序的复杂性。

但是SQL DBMS通常不轻易支持约束。因此，出于某些原因，我们可能会重新安排SQL或性能。但是首先要设计无空值。即选择直截了当的陈述&然后归一化。然后重新安排声音。 （SOme重排可能会取消规范化，但不是这种情况。）

空值复杂。

空值的一个问题是它们使表的含义复杂化。您的设计为空的表 group 包含使该语句为true的行：

  // * 
组[groupID]包含[nbStudents]个学生.... 
 AND（[advisorID为NULL，他们没有顾问
或[advisorID]不是NULL并且Advisor [facultyID]建议他们）
 AND（[examiner1ID为NULL，[examiner2ID]为NULL，并且他们没有审查员
，或者[examiner1ID]不为空，[examiner2ID]为NULL，[examiner1ID]对其进行检查
或[examiner1ID]为空，[examiner2ID]不为空，[examiner2ID]检查它们
或[examiner1ID]不为空，并且[examiner2ID]不为空
和[examiner1ID]检查他们和[examiner2ID]对其进行检查）
 * // 
 groups（groupID，nbStudents，...，advisorID，examinerID）
  

除非在查询时将空值切回上面的简单表，否则查询含义也很复杂。即查询提供的行可以使诸如此类的语句成为真。

最重要的是，当SQL中留空时，您会得到复杂的答案，而 not

人们对基表中的这些空值有直观的了解。但是首先要简单，合理地设计。稍后重新排列。确保正确切出无空零件，并在查询时保留无空零件。

i'm building a DB of a graduation projects management system. Students are divided into groups .There is groups table and faculty table. Each group has an advisor and two examiners. i'm confused here. Should i create 3 FKs from the the faculty table? 2 for examiners and 1 for advisor?

here is the SQL code:

 create table groups
(
    groupID             NUMBER                not null,
    nbStudents          NUMBER                not null,
    avgGPA              DOUBLE                NOT NULL,
    projectName         varchar(50)           not null,
    advisorID           NUMBER                
    examiner1ID         NUMBER  
    examiner2ID         NUMBER  
    primary key (groupID)
);
create table faculty
(
    name                varchar(30)           not null,
    facultyID           NUMBER(10)            not null,
    email               varchar(30)           not null,
    mobile              NUMBER(15)            not null,
    type                varchar               
    primary key (facultyID)
);

alter table groups
    add constraint FK_EX1 foreign key (examiner1ID)
       references faculty (facultyID) ;
alter table groups
    add constraint FK_EX1 foreign key (examiner2ID)
       references faculty (facultyID) ;
alter table groups
    add constraint FK_EX1 foreign key (advisorID)
       references faculty (facultyID) ;

解决方案

EDIT PENDING... see my first comment.

Just state the foreign keys as you find them.

A foreign key says that a value in a column in a table must appear as a value of a column in another (possibly the same) table where corresponding columns form a key. So in the given design just declare the FKs as you find them.

Although these aren't really FKs. First, in SQL a FK declaration actually declares a foreign superkey. Second, because those columns can be NULL. SQL says how it's going to check them, and it doesn't check when columns are NULL, and that's what you want. But that constraint just isn't a foreign (super)key. We just call it that in an SQL database.

Find statements that describe your application situtations then normalize.

It not non-normalized to have multiple columns per se. That is a common misconception.

However its generally contraindicated for at least an ideal design. Just find a parameterized statement parameterized by column names for every thing you need to say about a situation. Each statement gets a table.

// group [groupID] contains [nbStudents] students .... and has advisor [advisorID] and ...
groups(groupID,nbStudents,...,advisorID,examinerID)

The rows that make the statement true go in the table. Find all the statements you need to describe your application situations. Fill the tables with the rows that make their statements true.

Find simple statements and rearrange for NULL later.

Notice that the above statement is only true for rows with no NULLs. But you want to say sometimes that no faculty are in those roles.

Ideally you just want

// group [groupID] contains [nbStudents] students ... [projectName])
groups(groupID,nbStudents,...,projectName)
// [facultyID] advises [groupID]
advises(facultyID,groupID)
// [facultyID] examines [groupID]
examines(faculty,groupID)

With constraints about numbers of faculty per group. If you properly write a relational design without nulls then normalize you will get this sort of simple thing. Don't worry about the number of statements/tables. They just reflect the complexity of the application.

But SQL DBMSs generally don't suport constraints easily. So for certain reasons to do with SQL or performance we might to rearrange. But design null-free first. Ie pick straightforward statements & then normalize. Then rearrange soundly. (SOme rearranging might de-normalize, but not this particular case.)

Nulls complicate.

One problem with nulls is they complicate table meanings. Your design with nulls has table group holding the rows that make this statement true:

//*
    group [groupID] contains [nbStudents] students ....
AND (   [advisorID IS NULL  they have no advisor
    OR [advisorID] IS NOT NULL AND advisor [facultyID] advises them)
AND (   [examiner1ID IS NULL AND [examiner2ID] IS NULL and they have no examiner
    OR [examiner1ID] IS NOT NULL AND [examiner2ID] IS NULL AND [examiner1ID] examines them
    OR [examiner1ID] IS NULL AND [examiner2ID] IS NOT NULL AND [examiner2ID] examines them
    OR [examiner1ID] IS NOT NULL AND [examiner2ID] IS NOT NULL
       AND [examiner1ID] examines them AND [examiner2ID] examines them)
*//
groups(groupID,nbStudents,...,advisorID,examinerID)

Unless you cut out nulls back to the simple tables above when querying, your query meanings are complicated like this too. Ie queries give rows that make statements like that true.

On top of that when nulls are left in SQL gives you complex answers that do not mean "... and faculty unknown".

People have intuitive understanding of such nulls in base tables. But design first simply and soundly. Rearrange later. Be sure you properly cut out null-free parts and leave in null-free parts when you query.

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