CODEWITHSUNDEEP
sqloracle-databaseterminology
Mark Harrison
Mark Harrison

Reputation: 304604

relational terminology: foreign key source, destination?

I'm doing some FK analysis of our tables by making a directed graph representing FK dependencies and then traversing the graph. In my code, I name everything using directed graph terminology, but I'd like to have something a bit more "user friendly" in the report.

In this scenario:

create table t1(a varchar2(20));
alter table t1 add constraint t1_fk foreign key(a) references t2(b);

t1.a must exist in t2.b. So, what words should I use in the blanks?

t1 is the _______ of t2.
t2 is the _______ of t1.

Many TIA!

Upvotes: 5

Views: 1378

Answers (5)

jakodev
jakodev

Reputation: 1

In the explained scenario I think that the correct naming should be (technically speaking):
t2is the parent of t1
t1 is the child of t2

The table of the constrained column is called child, while the table of the referenced column is called parent.

Personally I dislike this naming, in SQL SERVER when you are querying for the sys.foreign_keys view the child table is called parent_object_id, while the referenced table is called referenced_object_id.. from the point of view of the constraint itself, could be right.. but at the end this is very confusing.

Upvotes: 0

WW.
WW.

Reputation: 24311

t1 is the parent of t2.
t2 is the child of t1.

What is the audience for this? If it's people that understand a relational schema, then that will probably do. If it is non-technical people, then generally I have documented in my modelling tool (ERWin) the meaning of the relationships specifically.

InvoiceLineItem is a part of Invoice.
Invoice has one or more InvoiceLineItems.

Or:

User must belong to a Business.
Business has zero or more Users.

Upvotes: 2

Tony Andrews
Tony Andrews

Reputation: 132640

You could adopt the following sentence form:

  • Each t1 row must be linked to exactly one t2 row
  • Each t2 row may be linked with any number of t1 rows, or none

Upvotes: 1

Tomalak
Tomalak

Reputation: 338316

I'd say something along the lines of

t1 is the master of t2. An ID must be in t1, before it can be mentioned in t2.
t2 is the slave of t1. It cannot refer to an ID that does not exist in t1.

Most non-technical people will grasp the master/slave terminology very intuitively.

Upvotes: 1

Vinko Vrsalovic
Vinko Vrsalovic

Reputation: 340306

I'd say (things between brackets are optional, but I'd use them)

[Column a of] table t1 references [column b of] table t2

and

[Column b of] table t2 is referenced by [column a of] table t1

?

I'd also specify the action that happens on delete/update if any.

Column b of table t2 is referenced by column a of table t1. 
Deleting a record in table t2 will delete matching records on table t1

Upvotes: 5

Related Questions