MySQL tutorial: Record insertion with foreign key constraint - mhulse/mhulse.github.io GitHub Wiki

Find user:

$ mysql -u root -p
Enter password:

SHOW DATABASES;
+---------------------------+
| Database                  |
+---------------------------+
| transition_assessment     |
+---------------------------+

SELECT User FROM mysql.user;
+------------------+
| User             |
+------------------+
| tat_user         |
+------------------+

Once user and database is found/known (password should be something you already know), type exit and make a backup:

$ mysqldump -u tat_user -p transition_assessment > transition_assessment.$(date +%Y%m%d%H%M%S).sql

Next, connect as that user:

$ mysql -u tat_user -p -D transition_assessment
Enter password:

List tables:

SHOW TABLES;
+---------------------------------+
| Tables_in_transition_assessment |
+---------------------------------+
| assessment                      |
| assessment_skill_area           |
| behavior_rating                 |
| hero                            |
| skill                           |
| skill_area                      |
| teacher_information             |
| user                            |
| user_detail                     |
| user_old                        |
| user_type                       |
+---------------------------------+

Shows detailed information regarding table structure (field + value types):

DESC skill_area;
+------------------------+--------------------------+------+-----+---------+----------------+
| Field                  | Type                     | Null | Key | Default | Extra          |
+------------------------+--------------------------+------+-----+---------+----------------+
| skill_area_id          | int(11)                  | NO   | PRI | NULL    | auto_increment |
| skill_area_name        | varchar(100)             | YES  |     | NULL    |                |
| skill_area_description | varchar(255)             | YES  |     | NULL    |                |
| skill_area_type        | enum('Teacher','Parent') | YES  |     | NULL    |                |
+------------------------+--------------------------+------+-----+---------+----------------+

Next, we’ll insert a new record into the table (use single quotes for MySQL strings):

INSERT INTO skill_area (skill_area_name)
VALUES ('Functional Academics');
Query OK, 1 row affected (0.01 sec)

Next, we need to enter more date, but use the ID of the record we just inserted:

SELECT skill_area_id FROM skill_area WHERE skill_area_name='Functional Academics';
+---------------+
| skill_area_id |
+---------------+
|             2 |
+---------------+
1 row in set (0.00 sec)

Using the skill_area_id above, we’ll write some SQL to insert new records into another table using the skill_area_id as a foreign key constraint.

First, let’s look at the structure of the other table:

DESC skill;
+---------------+--------------+------+-----+---------+----------------+
| Field         | Type         | Null | Key | Default | Extra          |
+---------------+--------------+------+-----+---------+----------------+
| skill_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| skill_text    | varchar(255) | NO   |     | NULL    |                |
| skill_order   | int(11)      | NO   |     | NULL    |                |
| skill_area_id | int(11)      | NO   |     | NULL    |                |
+---------------+--------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)

Now that we know what it looks like, let’s write the SQL:

INSERT INTO skill (
    skill_area_id,
    skill_order,
    skill_text
)
VALUES (2, 1, 'Reads his or her name when printed.'),
       (2, 2, 'Writes or prints his or her first and last name.'),
       (2, 3, 'States the days of the week in order.'),
       (2, 4, 'Reads and obeys common signs (for example, Do Not Enter, Exit, Stop).'),
       (2, 5, 'Answers simple questions about a story read to him or her.'),
       (2, 6, 'Locates important dates on a calendar (for example, birthdays or holidays).'),
       (2, 7, 'Reads and follows instructions for completing classroom projects or activities.'),
       (2, 8, 'Reads school lunch menus.'),
       (2, 9, 'Tells time correctly, using a watch or a clock with hands.'),
       (2, 10, 'Writes his or her address, including zip code.'),
       (2, 11, 'Measures length and height.'),
       (2, 12, 'Uses a scale to weigh objects.'),
       (2, 13, 'Combines coins to produce the correct amount of money (for example, a quarter plus a dime equals 35 cents).'),
       (2, 14, 'Reads and follows a daily classroom or work schedule, without needing to be reminded by another person.'),
       (2, 15, 'Writes and sends letters, personal notes, or emails.'),
       (2, 16, 'Uses printed or Internet resources to find information (for example, in dictionaries and encyclopedias).'),
       (2, 17, 'Follows a favorite interest or current event by reading about it in newspapers, books, or other materials, or on the Internet.'),
       (2, 18, 'Uses the index in a book to locate desired topics.'),
       (2, 19, 'Takes notes during class.'),
       (2, 20, 'Writes a list of school supplies or assignments.'),
       (2, 21, 'Reads important information (for example, class registration and announcements or school conduct policies).'),
       (2, 22, 'Completes written forms to apply for jobs.');

Copy and paste the above SQL into the command line and hit enter.

You now have data, with FKs, in your database!