Part 2. The database
In this second part, we will create the database from phpMyAdmin.
Start by creating a database named liste2courses encoded in
utf8mb4_unicode_here.
Creating tables
The database will consist of 2 tables :
listscontains the names of the shopping listsarticlescontains the list of articles
Create the two tables according to the following representation. In each table,
id is a primary key with auto-incrementing.
Relationship between tables
MySQL is a relational database language. In other words, it is It is possible to specify a link between two tables. This link has many advantages, such as the cascading suppression of rows from two distinct tables that are linked by a relationship. MySQL also checks that of the database always remain consistent.
In phpMyAdmin, go to the Structure table of the articles table. Click
button on the Relational View button and create a link between the articles.id_list fields.
and `lists.id' as in this example:
The result is a link between the two tables. The id_list field of the articles table
must match an existing primary key in the articles table:
If you try to insert a new line without specifying a value for id_list that corresponds to a row in the articles table, mySQL will not create the row and
will return one of the following errors:
#1366 - Incorrect integer value: '' for column 'id_list' at row 1.#1452 - Cannot add or update a child row: a foreign key constraint fails ('liste2courses'.'articles', CONSTRAINT 'articles_ibfk_1' FOREIGN KEY ('id_list') REFERENCES 'lists' ('id') ON DELETE CASCADE ON UPDATE CASCADE)
This last error mentions that the foreign key violates the constraint that you you added earlier.
See also
- Part 1. The home page
- Part 3. PHP library
- Part 4: Creating the page
- Part 5. Adding an item to the list
- Part 6. Displaying the shopping list
- Part 7. Removal of an item
- Part 8. Error Handling
- Part 9. Improvements
