oracle 插入具有多个值的外键
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9284344/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Inserting a foreign key with multiple values
提问by typedef1
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.
我想知道，是否有可能创建一个表，其中我有一个接受外键但同一行可能有多个值的表。
For example:
例如：
Employee(id,name,skillid);
Skill(Skillid,skillname);
here an instance for Employee could be
这里 Employee 的一个实例可能是
Employee(311,"john", (01,02) );
Skill (01,Java); Skill (02,C++);
I implemented the same making primary key for the table as (is,skillid)
我为表实现了与 (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));
and another table :
和另一个表：
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));
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.
在这种情况下，我无法将 Movie_ID 用作外键，因为它引用的表将 2 个事物的组合作为其主键。而且我没有在 Awards 表中使用这些属性中的第二个。
Any hints how this can be implemented ?
任何提示如何实现？
回答by Ben
Unfortunately you've just asked the age old question;
不幸的是，您刚刚问了一个古老的问题；
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)
);
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?.
正如你所看到的，这不是一件特别漂亮的事情，而且破坏了正常化；如果您希望您的员工（或您的员工当然希望）拥有 3 项技能，会发生什么？还是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 );
回答by Justin Cave
If you want to create a composite primary key in the parent table, you would need to create a composite foreign key in the child table. In other words, if you want the parent table to use a natural primary key comprised of three different columns
如果要在父表中创建复合主键，则需要在子表中创建复合外键。换句话说，如果您希望父表使用由三个不同列组成的自然主键
CREATE TABLE car_type (
make VARCHAR2(100),
model VARCHAR2(100),
year NUMBER,
PRIMARY KEY pk_car_type( make, model, year )
);
then the child table would need to have all three columns as well
那么子表也需要拥有所有三列
CREATE TABLE car (
vin VARCHAR2(17) PRIMARY KEY,
make VARCHAR2(100),
model VARCHAR2(100),
year NUMBER,
FOREIGN KEY fk_car_car_type( make, model, year )
REFERENCES car_type( make, model, year )
);
Using multiple columns in your foreign key definitions generally gets painful as schemas get larger and you need to join more tables together which is why people introduce synthetic primary keys (i.e. meaningless keys that get populated by a sequence). That lets you simplify things
在外键定义中使用多个列通常会随着模式变大而变得痛苦，并且您需要将更多表连接在一起，这就是人们引入合成主键（即由序列填充的无意义键）的原因。这让你可以简化事情
CREATE TABLE car_type (
car_type_id NUMBER PRIMARY KEY,
make VARCHAR2(100),
model VARCHAR2(100),
year NUMBER,
UNIQUE uk_car_type( make, model, year )
);
CREATE TABLE car (
vin VARCHAR2(17) PRIMARY KEY,
car_type_id NUMBER REFERENCES car_type( car_type_id )
);
回答by APC
If (movie,cast)is the primary key of the parent table any referencing table has to include bothcolumns in the foreign key. That's just the rules.
如果(movie,cast)是父表的主键，则任何引用表都必须在外键中包含这两列。这只是规则。
There are two ways to resolve this. Either you have got the primary key wrong, in which case you need to amend the constraint on the MOVIES table. Alternatively you need to add the CAST column to the AWARDS table.
有两种方法可以解决这个问题。要么是主键错误，在这种情况下，您需要修改 MOVIES 表上的约束。或者，您需要将 CAST 列添加到 AWARDS 表中。
Composite keys are a pain in the neck when it comes to foreign keys. This is why many practitioners prefer to have a surrogate (or synthetic) primary key, so that the child tables only have to reference a single column. The original - composite - key is still enforced, but as a unique key.
当涉及到外键时，复合键是一个令人头疼的问题。这就是为什么许多从业者更喜欢使用代理（或合成）主键，这样子表只需引用单个列。原始 - 复合 - 键仍然强制执行，但作为唯一键。
