插入具有多个值的外键 [英] Inserting a foreign key with multiple values

问题描述

我想知道，是否有可能创建一个表，其中我有一个表接受外键，但可能有同一行的多个值。

I was wondering, is there any possibility to create a table where i have a table which accepts a foreign key but may have multiple values for the same row.

例如：

Employee(id,name,skillid);
Skill(Skillid,skillname);

这里Employee的实例可以是

here an instance for Employee could be

Employee(311,"john", (01,02) );
Skill (01,Java); Skill (02,C++);

我实现了表的相同主键作为（is，skillid）

I implemented the same making primary key for the table as (is,skillid)

但是在一个情况下我有一个表：

But in a case i have a table :

create table Movie (Movie_ID varchar(5),                              
                            Cast varchar(5),
                            foreign key(Cast) references Person(Person_ID), 
                            primary key(movie_id, Cast));

和另一个表格：

create table Awards  (Award_Id varchar(5),
                                Person_Id varchar(5), 
                                Movie_Id varchar(5),
                                Award_Name  varchar(30),
                                Year number(4),
                                Organization_Id varchar(5),
                                primary key (Award_id,year,Organization_Id),
                                foreign key(Person_Id) references Person(Person_ID),
--                                foreign key(Movie_ID) references Movie(Movie_ID),
                                foreign key(Organization_Id) references Organization(Organization_Id));

在这种情况下，我不能使用Movie_ID作为外键，因为它引用的表有结合2件事为主要关键。我不是在奖表中使用这些属性的第二。

In this case i am not able to use Movie_ID as a Foreign key since the table it is referencing has combination of 2 things as its primary key. And i am not using 2nd of those attribute in the Awards table.

任何提示如何实现？

推荐答案

只是问老人的问题;

如何将两个项目放入一列？

How do I put two things in one column?

答案当然是，你不是;你有两列。要扩展您的员工示例，您的员工表将变为：

The answer is, of course, that you don't; you have two columns. To extend your employee example your employee table would become:

create table employees (
   id number
 , name varchar2(4000)
 , skill_1 number
 , skill_2 number
 , constraint employee_pk primary key (id)
 , constraint employee_skill_1_fs 
      foreign key ( skill_1 ) 
   references skills(skillid)
 , constraint employee_skill_2_fs 
      foreign key ( skill_2 ) 
   references skills(skillid) 
   );

正如你可以看到，这不是一个特别漂亮的事情，如果你想让你的员工（或你的员工想要当然）有3个技能会发生什么？或10？

As you can see this isn't a particularly pretty thing to do and breaks normalisation; what happens if you want your employee ( or your employee wants of course ) to have 3 skills? Or 10?.

最好创建第三个表，并通过单个主键和外键执行所有连接;所以你会有

It would probably be better to create a third table and do all your joins by a single primary and foreign key; so you would have

employees ( employee_id, ..., pk employee_id);
employee_skills ( employee_id, skill_id, ..., pk employee_id, skill_id, fk skill_id );
skills ( skill_id, description, ..., pk skill_id );

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