如何在表中插入外键 [英] how to insert foreign key in a table

问题描述

我只是想确定在理解 PRIMARY 和 FOREIGN 我们可以这样说：

我们有 CITY（ID- PK，CODE，NAME），我们有表 PERSON（ID-PK，NAME，LASTNAME，CITY_ID-FK）

我很困惑，如果在这种情况下用户需要在表中输入外键的人？如果不知道哪个城市需要应用于用户？如果用户需要输入一个外键为什么要这么做，那么因为这样我们就留下了很多用户端的操作空间（等错误的ID输入...）

如何连接这两个表？什么将是一个可识别的连接参考？或者在这个例子中用户需要输入一个外键？

我正在使用ORACLE数据库。我会很感激任何解释和例子。

解决方案

我不完全确定你的意思是错误的ID输入，但我假设你的意思是一个无效的ID，而不仅仅是一个错误（比如说有人在一个不同的城市，他们真正在哪里）。

外键约束意味着它们在 person 表中为 city_id 输入的值具有作为主键在 city 表中存在。他们不能把任何旧的值作为 city_id ，只有有效的值。如果不删除/更改 person 表中的引用，则相应的 city 行不能被删除（例如，更新为一个不同的有效值），或者 - 不太可能在这种情况下 - 级联删除，以便 city 的任何 person 被删除。

所以可以说你创建你的表格为：

  create table city（id number primary key，code varchar2（2），name varchar2（30））; 
 
 create table person（id number，name varchar2（30），last_name varchar2（30），
 city_id number not null references city（id））; 
  

您在 city 表中有三条记录：
$ b $ pre $ insert into city（id，name）values（1，'New York'）;
插入city（id，name）值（2，'London'）;
插入城市（id，name）值（3，'Paris'）;


然后您可以添加一个 person 通过包括该城市的ID在纽约：

  insert into person（id，name，last_name，city_id）
值（1，'Michael'，'Bloomberg'，1）; 
  

（

您并不是在匹配 city city 记录的单一更新，你不会如果您试图删除个人记录在该城市的人。

c> city 记录纽约，你会得到一个错误（ORA-02292）说有一个子记录存在。您可以更新个人记录，让 city_id 为2或3，然后可以删除纽约。这个想法是，你不能意外地做这件事情，留下孤立的数据 - person_id 指向一个城市不再存在。

如果您尝试使用 city_id创建一个 person 记录值不符合 city.id 值：

  insert into person（id，name，last_name，city_id）
 values（2，'Elvis'，'Presley'，4）; 
  

...那么你会得到一个错误（ORA-02291）在 city 表中有一个匹配的 id 值不存在。





有关外键的更多信息，请参见数据库概念指南。

I just want to make sure that I understand PRIMARY and FOREIGN key relation before searching for some finished answers on the internet.

Let's say this:

We have table CITY(ID-PK,CODE,NAME) and we have table PERSON(ID-PK,NAME,LASTNAME,CITY_ID-FK)

I'm confused, if in this case user needs to enter a foreign key in the table person? If not how know which city needs to be applied to the user? If user needs to enter a foreign key why to have it then because in that way we are leaving a lot of space for manipulation from the user side (etc. wrong ID input...)

How to connect those two tables? What would be a recognizable reference to connect? Or in this example user needs to input a foreign key?

I'm using ORACLE database. I would appreciate any explanation and example.

解决方案

I'm not entirely sure what you mean by 'wrong ID input', but I'll assume you mean an ID that that isn't valid, rather than just a mistake (like saying someone is in a different city to where they really are).

The foreign key constraint means that the value they enter in the person table for city_id has to exist as a primary key in the city table. They cannot put any old value in as a city_id, only valid values. And the corresponding city row cannot then be deleted without either removing/changing the reference in the person table (e.g. updating to a different valid value), or - unlikely in this case - cascading the delete so that any person records for the city are deleted.

So lets say you create your tables as:

create table city (id number primary key, code varchar2(2), name varchar2(30));

create table person (id number, name varchar2(30), last_name varchar2(30),
    city_id number not null references city(id));

You have three records in your city table:

insert into city (id, name) values (1, 'New York');
insert into city (id, name) values (2, 'London');
insert into city (id, name) values (3, 'Paris');

Then you can add a person who lives in New York by including the ID of that city:

insert into person (id, name, last_name, city_id)
values (1, 'Michael', 'Bloomberg', 1);

(SQL Fiddle)

You aren't denormalising the data in the matching city record, so if it New York decided to change its name back to New Amsterdam, say, that would be a single update to the city record and you wouldn't have to touch any person records for people in that city.

If you tried to delete the city record for New York, you'd get an error (ORA-02292) saying that a child record existed. You could update the person record to have a city_id of 2 or 3, and would then be able to delete New York. The idea is that you can't do this by accident and leave orphaned data behind - a person_id pointing to a city that no longer exists.

If you tried to create a person record with a city_id value that doesn't match a city.id value:

insert into person (id, name, last_name, city_id)
values (2, 'Elvis', 'Presley', 4);

... then you'd get an error (ORA-02291) that the parent key - that is, a matching id value in the city tables - doesn't exist.

You can read more about foreign keys in the database concepts guide.

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