Hello. Faced MySQL. The course is introductory, but still I would like to know at least the basics. Found one example:

CREATE TABLE usr ( usr_id INT AUTO_INCREMENT NOT NULL, FIRST VARCHAR(25) NOT NULL, surname VARCHAR(50) NOT NULL, PRIMARY KEY(usr_id) ) ENGINE=InnoDB CHARACTER SET=UTF8; CREATE TABLE product ( prod_id INT AUTO_INCREMENT NOT NULL, name VARCHAR(40) NOT NULL, descr VARCHAR(255) NOT NULL, PRIMARY KEY(prod_id) ) ENGINE=InnoDB CHARACTER SET=UTF8; CREATE TABLE invoice ( inv_id INT AUTO_INCREMENT NOT NULL, usr_id INT NOT NULL, prod_id INT NOT NULL, quantity INT NOT NULL, PRIMARY KEY(inv_id), FOREIGN KEY (usr_id) REFERENCES usr(usr_id) ON UPDATE CASCADE ON DELETE RESTRICT, FOREIGN KEY (prod_id) REFERENCES product(prod_id) ON UPDATE CASCADE ON DELETE RESTRICT ) ENGINE=InnoDB CHARACTER SET=UTF8;

The essence is as follows: a table of goods, customers and a table of orders. Questions:

  1. The primary key and the external one, as I understand it, are the same for different tables: for example, prod_id for product tables is PK, and for orders, external?
  2. The line usr_id INT AUTO_INCREMENT NOT NULL, means the creation of the primary key table buyers, ID increases automatically and can not be zero?
  3. What makes ENGINE=InnoDB CHARACTER SET=UTF8; (something with encoding) and is it necessary?
  4. In this case, the parents will be buyers and products, the order table is a subsidiary?
  5. The FOREIGN KEY (usr_id) REFERENCES usr(usr_id) ON UPDATE CASCADE ON DELETE RESTRICT, entry FOREIGN KEY (usr_id) REFERENCES usr(usr_id) ON UPDATE CASCADE ON DELETE RESTRICT, shows that the change in record is the product displayed in orders, and the change in the order is not displayed on the product (it was written on the website)? Did I understand correctly, if you remove the product, then the order associated with it will be removed, and if, for example, the buyer canceled the order, then the product remains in the table of goods? And these features (changes) are indicated always and only after the foreign keys of the parent tables for this child when it is created?
  6. The last question is in the connections. I read that there are different connections in the database, for example, one to many, many to many, and one to one. As in the case of the examples mentioned above, connections are created? After all, the buyer can order not one product, but two or more? This is not specifically indicated or something I do not see? From the very beginning I thought that in essence one record corresponds to another. Or, it is precisely thanks to these notorious keys that a connection is created: one buyer can have several goods - one to many. Right lmi, I understand if I express myself correctly, if so.

PS: perhaps many questions will seem profane, but for this, after all, a forum has been created. Thanks in advance to those who have enough patience to answer me.

  • one
    SO is not a forum, and it was not created for this. A short answer to almost all of your questions is “no, it’s not.” If you want to understand - find another tutorial. Preferably not according to MySQL - Pavel Mayorov
  • everything is wrong, is that all? - Muscled Boy
  • I did not answer your question, I wrote a comment. Wait, maybe someone will answer. But, generally speaking, questions from 6 parts are not welcome here. - Pavel Mayorov
  • and the notorious words: "Do not be ashamed to ask - do not know more shameful" promoted by the site only smoke, which is easy to dispel? Of course, excuse me, but I understand that the questions may be stupid, but not at all in the subject, and the answers from the category “you are a cretin and how dare you ask about this” should not be written here - Muscled Boy
  • Such thorough enough questions, it is better not to look at SO. The problem is that they require an understanding of the basics, and one cannot get it from the short answers to SO. Therefore, it is better to find a good book on the database and read. - cVoronin

1 answer 1

Two entities are implemented in your scheme: customers and goods. The relationship between them is many-to-many, it is carried out using an adjacent table: orders.

  1. Foreign keys in the table "orders" just indicate the identifiers in the tables of buyers and products. For example, a record (1, 2, 3, 4) in the table will show orders: 1 order ID, 2 user ID, 3 product ID, 4 quantity.
  2. Yes, this entry means that the identifier field cannot be zero and increases automatically.
  3. Indicates the encoding and engine. As far as I know, it is not necessary, but desirable, especially encoding, because by default it is not UTF-8
  4. Yes. Details see p. 1
  5. CASCADE means that changing a record in the parent table will cause a change in the child, and RESTRICT means that the database will not allow changes to be made. That is, in your case, when you try to delete a product, an error will occur that the associated data exists in the order table

  6. The adjacent many-to-many link table takes into account that both the buyer can order multiple items and one item can be ordered by multiple buyers. For example, entries in the order table:

    (1, 1, 1, 5) - user 1 ordered goods 1 in a quantity of 5 pieces

    (2, 1, 2, 6) - user 1 ordered the goods 2 in the amount of 6 pieces

    (3, 2, 2, 8) - user 2 ordered goods 2 in a quantity of 8 pieces

  • “Orders” here is not a link, but a separate entity with its own attributes. - Pavel Mayorov
  • "CASCADE means that changing the record in the parent table will cause a change in the child" - does not mean. Certainly not any change. - Pavel Mayorov
  • "Yes, this entry means that the identifier field cannot be zero and increases automatically" does not mean. Probably you wanted to say "empty" and not "zero"? - Pavel Mayorov
  • exactly wanted to show - Muscled Boy
  • @ 1000ISLANDS, I'll see your answer now - Muscled Boy