Let there be three tables: students, housing and some other. In the housing field: auto- id , town , street , flat_number . To record in the housing did not repeat should make UNIQUE KEY (id, town, street, flat_number) and did I write correctly? After this, we create a PK ( id ) and thus can there already be links to the records from the housing in the third table and there are no errors, as if I had created two primary keys in the housing? The second. And if there were no third table with links, would it be possible to do it through PRIMARY KEY, and not UNIQUE KEY?

    1 answer 1

    If I understand you correctly: no unique key should be different.

    It should be: UNIQUE KEY (town, street, flat_number) otherwise the lines with the same town, street, flat_number can be repeated with different id.

    "As if I created two primary ones in a dwelling" - tables cannot have two primary keys, but you can emulate a primary key. PRIMARY KEY is also a UNIQUE + NOT NULL. If you need to emulate a primary key, you can hang on this field / fields unique and not null (not all fields must be in the key).

    "Second. And if there were no third table with links, it could be done through PRIMARY KEY, not UNIQUE KEY" --- if you do not need the uniqueness of housing, and the table of students is given just like that (it does not depend on housing nor from the 3rd table), then yes. But why, in this case, the table of students is mentioned in the question if nothing depends on it?

    • But you can remove the id of the entry in the housing, because it means that one entry is assigned to one student? - up-and-coming
    • one
      From the point of view of relational algebra, id is a meaningless field. The primary key is (town, street and flat_number), other fields, if any, depend not on id but on these 3 spills. Id, in similar tables, is used to speed up join and some samples (merging 2 columns 1 column each with int is simpler than 3 rows) and for programmer's convenience (for the same reasons it is easier to update and delete individual rows). - Arnial
    • Understood thanks for your reply) - up-and-coming