![]() ![]() FOREIGN KEY (job_title_id ) REFERENCES job_titles (job_title_id )Įnsure you get the following output confirming you’ve created the table:.employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,.To create the employees table, run the following command: Again, make sure to include the keyword ENGINE = InnoDB to take advantage of the InnoDB storage engine as outlined in Step 1. To improve the speed when retrieving data from the two interlinked tables, use the statement INDEX (job_title_id) to index the job_title_id column. Therefore, a VARCHAR(50) data type should work for the first_name, last_name, and phone fields. This data type is also perfect for the phone number. You’re capturing the employees’ names using the first_name and last_name text fields with a maximum length of 50 characters. In the following employees table, the employees_id is the PRIMARY KEY and you’ve used the AUTO_INCREMENT keyword to generated new employees_ids as you insert new values. For consistency purposes, you’re using the BIGINT data type, which you used for the related columns. You’re achieving this by issuing the statement FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id). The job_title_id column in the employees table points back to the same column in the job_titles table. This table holds records for all staff members in the company. statement, make sure your command was completed successfully by confirming the following output: job_title_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,Īfter running the CREATE TABLE job_titles.This is a transaction-ready general-purpose storage engine that handles concurrency while still ensuring high reliability and high performance in your database application.Įxecute the following command to create the job_titles table: You will define this data type with the syntax VARCHAR(50).įollowing CREATE TABLE command, instruct MySQL to use the InnoDB database engine by including the ENGINE = InnoDB keyword. This column stores string values with the maximum length at 50 characters. In the CREATE TABLE command, include a job_title_name column that stores a human-readable value for a job title. You’re using the AUTO_INCREMENT keyword to let MySQL automatically assign sequential numeric values every time you insert a new job title. The job_title_id is a primary key that uniquely identifies each job title in your database using the BIGINT data type that can accommodate up to 2^63-1 records. This table works as a look-up table for all job titles available in your database. Next, set up a job_titles table using the CREATE TABLE command. Next, issue the following SQL command to create a sample company_db database:Ĭonfirm the following output to make sure the database has been created without errors. When prompted, enter the non-root user account password of your MySQL server and press ENTER or RETURN to proceed. Replace example_user with the exact name for your non-root account. Then, execute the following command to log in to your MySQL server. You’ll also insert some sample data that you’ll use to work with foreign keys throughout the guide.īegin by connecting to your server as a non-root user. In this step, you’ll create a sample database and set up a few tables. Step 1 - Setting Up a Sample Database and Tables Review the tutorial on How To Install MySQL on Ubuntu 20.04 server to set up and configure a database server. Follow the Initial Server Setup for Ubuntu 20.04 guide to create a non-root user and enable a firewall.Ī MySQL database server. To complete this tutorial, you’ll require the following:Īn Ubuntu 20.04 server secured with a non-root sudo user and a basic firewall. Although this guide is tested on a MySQL database, it can still work in other SQL-based databases with just a few syntax changes. ![]() In this guide, you’ll enforce referential integrity with foreign keys on your database. Referential integrity is also useful for preventing entries of invalid data in a multi-user database environment. Referential integrity ensures all data references are valid and prevents inconsistent entries or orphaned records. For instance, you can have an employees table with a column named job_title_id that refers back to a lookup table named job_titles.Īnother example can be demonstrated in an e-commerce database where you might create a category_id column in a products table that links back to a parent products_categories table. ![]() In this use case, this is where referential integrity comes into play. A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. When working on a large SQL project, you must maintain the accuracy and consistency of data across all tables with foreign keys. The author selected Apache Software Foundation to receive a donation as part of the Write for DOnations program. ![]()
0 Comments
Leave a Reply. |