-1

In database terms, when i add a new foreign key, insert a record for that foreign key and update the existing record, what is the process called? My goal is to be able to find answers more effectively.

//create temporary linking key
alter table example add column example_foreign_key int unsigned null;

//contains more fields
insert into example_referenced_table (example_id, ...) 
select id, ... 
from example 
join ...;

//link with the table
update example join example_referenced_table on example_id = example.id
set example.example_foreign_key = example_referenced_table.id;

//drop linking key
alter table example_referenced_table drop column example_id;
Olavi Sau
  • 1,647
  • 13
  • 20

1 Answers1

1

It looks like you're substituting one surrogate identifier for another. Introducing a surrogate key is sometimes (incorrectly) called normalization, so you may get some hits on that term.

In rare cases, normalization requires the introduction of a surrogate key, but in most cases, it simply decomposes a relation (table) into two or more, in such a way that no information is lost.

Surrogate keys are generally used when a natural or candidate key doesn't exist, isn't convenient, or not supported (e.g. composite keys are often a problem for object-relational mappers). For criteria on picking a good primary key, see: What are the design criteria for primary keys

There's little value in substituting one surrogate identifier for another, so the procedure you demonstrate has no proper name as far as I know, at least in the relational model.

If you mean to introduce a surrogate key as an identifier of a new entity set to which the original attribute is transferred, that's close to what Peter Chen called shifting a value set from the lower conceptual domain to the upper conceptual domain. You can find more information in his paper "The entity-relationship model - A basis for the enterprise view of data".

As for your question's title, it's not wrong to say that you're adding a relationship to a table (though that wording mixes conceptual and physical terms), but note that in the entity-relationship model, a relationship is represented by a two or more entity keys in a table (e.g. (id, example_foreign_key) in the example table) and not by a foreign key constraint between tables. The association of relationships with foreign key constraints came from the network data model, which is older than both the relational and entity-relationship models.

reaanb
  • 9,806
  • 2
  • 23
  • 37
  • It seems that I have not given a proper example, the example_referenced_table would usually contain more fields other than the id. The example just examplifies the linking process. – Olavi Sau Jun 13 '17 at 06:50
  • > "though that wording mixes conceptual and physical terms" Elaborate? – Olavi Sau Jun 13 '17 at 06:57
  • Entity-relationship models are conceptual data models, providing a reasonably intuitive (but not logically complete) discipline to organize knowledge similarly to the ontology of the subject area. ER models are then mapped to relations (logical data model) which are represented by tables (physical data model). Thus, saying that you're adding a relationship to a table is mixing different levels of description. Physically, we would add columns to an existing or new table to represent the new relationship. – reaanb Jun 13 '17 at 08:54