Hey.

Question on sql. I do not understand about the identifying and non-identifying links (in the workbench this is a solid and dashed line). Table connections are implemented via column keys.

I read many articles and did not understand what an identifying and non-identifying link is. Please give a clear example.

It is written that "an identifying relationship is a relationship in which one or several columns of a key (recall that a key can consist of several columns) specified in the link (i.e. those in the parent table) are used as the whole primary key or parts of the primary key of the referencing (ie, child) table. "

I don’t understand how a key can be ** multiple columns ** at once . I understand about one column - for example, in the main table there is a column with the teacher number (1, 2, 3 ..), and in the reference table these numbers are decoded (1 is Ivanov, 2 is Petrov ...).

    1 answer 1

    Easier to explain with an example.

    You have a table "House" and a table "Apartment".

    In the first table, the key will be a set of columns: "Country - Region - City - Street - House Number". None of these columns individually is a key (does not define a unique house), the key is only their full set.

    In the second table, the key will be the set of columns "Country - Region - City - Street - House Number - Apartment Number".

    So the key of the child object fully includes the key of the parent object, therefore the association is identifying. But if we add the table "Floors" with the numbers "1, 2, 3", etc. and in the “Flat” table we add a field that will refer to the “Floors” table, but will not be included in the key, then such a link will not be identifying.

    Just in case, this is not an example of how to design a base. This is not a very good pattern, it is better to enter a surrogate key from one column for ease of use. This example is given purely to describe concepts.