Geek Answers Handbook

Using Links in MYSQL

I got an answer to another question:

Database schema for chats?

This is a great answer, but I don't understand the link bit. I can execute SQL statements, but never used references.

  • What are they used for?
  • How are they used?
  • Give an example
+7
sql mysql foreign-keys
source share
2 answers

The REFERENCES keyword is part of the foreign key constraint , and this forces MySQL to require that the values ​​(values) in the specified column) of the referenced table are also present in the specified column (s) of the referenced table.

This prevents foreign keys from referencing identifiers that do not exist or have been deleted, and if necessary, they may not delete rows while they are still referenced.

A concrete example: if each employee must belong to a department, then you can add a foreign key constraint with the employee.departmentid link department.id .

Run the following code to create two test tables, tablea and tableb , where the a_id column in tableb refers to the primary key of tablea . tablea populated with several lines.

 CREATE TABLE tablea ( id INT PRIMARY KEY, foo VARCHAR(100) NOT NULL ) Engine = InnoDB; INSERT INTO tablea (id, foo) VALUES (1, 'foo1'), (2, 'foo2'), (3, 'foo3'); CREATE TABLE tableb ( id INT PRIMARY KEY, a_id INT NOT NULL, bar VARCHAR(100) NOT NULL, FOREIGN KEY fk_b_a_id (a_id) REFERENCES tablea (id) ) Engine = InnoDB;

Now try the following commands:

 INSERT INTO tableb (id, a_id, bar) VALUES (1, 2, 'bar1'); -- succeeds because there is a row in tablea with id 2 INSERT INTO tableb (id, a_id, bar) VALUES (2, 4, 'bar2'); -- fails because there is not a row in tablea with id 4 DELETE FROM tablea WHERE id = 1; -- succeeds because there is no row in tableb which references this row DELETE FROM tablea WHERE id = 2; -- fails because there is a row in tableb which references this row

Important Note: Both tables must be InnoDB tables or the constraint is ignored.

+12
source share

The REFERENCES keyword shows a foreign key constraint, which means that:

 FOREIGN KEY (`chat_id` ) REFERENCES `chats`.`chat` (`id` )

... the chat_id column in the current table can contain only those values ​​that already exist in the chat table, the id column.

For example, if the CHAT.id column contains:

 id ---- a b c

.. you cannot add any values ​​other than a / b / c to the chat_id column.

+2
source share

More articles:


All Articles