One place for hosting & domains

      How to Set Up SSH Keys on Debian 11


      Not using Debian 11?


      Choose a different version or distribution.

      Introduction

      SSH, or secure shell, is an encrypted protocol used to administer and communicate with servers. When working with a Debian server, chances are you will spend most of your time in a terminal session connected to your server through SSH.

      In this guide, we’ll focus on setting up SSH keys for a vanilla Debian 11 installation. SSH keys provide an easy, secure way of logging into your server and are recommended for all users.

      Step 1 — Create the RSA Key Pair

      The first step is to create a key pair on the client machine (usually your computer):

      By default ssh-keygen will create a 3072-bit RSA key pair, which is secure enough for most use cases (you may optionally pass in the -b 4096 flag to create a larger 4096-bit key).

      After entering the command, you should see the following output:

      Output

      Generating public/private rsa key pair. Enter file in which to save the key (/your_home/.ssh/id_rsa):

      Press enter to save the key pair into the .ssh/ subdirectory in your home directory, or specify an alternate path.

      If you had previously generated an SSH key pair, you may see the following prompt:

      Output

      /home/your_home/.ssh/id_rsa already exists. Overwrite (y/n)?

      Warning: If you choose to overwrite the key on disk, you will not be able to authenticate using the previous key anymore. Be very careful when selecting yes, as this is a destructive process that cannot be reversed.

      You should then see the following prompt:

      Output

      Enter passphrase (empty for no passphrase):

      Here you optionally may enter a secure passphrase, which is highly recommended. A passphrase adds an additional layer of security to prevent unauthorized users from logging in. To learn more about security, consult our tutorial on How To Configure SSH Key-Based Authentication on a Linux Server.

      You should then see the following output:

      Output

      Your identification has been saved in /your_home/.ssh/id_rsa. Your public key has been saved in /your_home/.ssh/id_rsa.pub. The key fingerprint is: SHA256:5E2BtTN9FHPBNoRXAB/EdjtHNYOHzTBzG5qUv7S3hyM root@debian-suricata The key's randomart image is: +---[RSA 3072]----+ | oo .O^XB| | . +.BO%B| | . = .+B+o| | o o o . =.| | S . . =| | o.| | .o| | E o..| | . ..| +----[SHA256]-----+

      You now have a public and private key that you can use to authenticate. The next step is to place the public key on your server so that you can use SSH-key-based authentication to log in.

      Step 2 — Copy the Public Key to Debian Server

      The quickest way to copy your public key to the Debian host is to use a utility called ssh-copy-id. Due to its simplicity, this method is highly recommended if available. If you do not have ssh-copy-id available to you on your client machine, you may use one of the two alternate methods provided in this section (copying via password-based SSH, or manually copying the key).

      Copying Public Key Using ssh-copy-id

      The ssh-copy-id tool is included by default in many operating systems, so you may have it available on your local system. For this method to work, you must already have password-based SSH access to your server.

      To use the utility, you simply need to specify the remote host that you would like to connect to and the user account that you have password SSH access to. This is the account to which your public SSH key will be copied.

      The syntax is:

      • ssh-copy-id username@remote_host

      You may see the following message:

      Output

      The authenticity of host '203.0.113.1 (203.0.113.1)' can't be established. ECDSA key fingerprint is fd:fd:d4:f9:77:fe:73:84:e1:55:00:ad:d6:6d:22:fe. Are you sure you want to continue connecting (yes/no)? yes

      This means that your local computer does not recognize the remote host. This will happen the first time you connect to a new host. Type “yes” and press ENTER to continue.

      Next, the utility will scan your local account for the id_rsa.pub key that we created earlier. When it finds the key, it will prompt you for the password of the remote user’s account:

      Output

      /usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed /usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys username@203.0.113.1's password:

      Type in the password (your typing will not be displayed for security purposes) and press ENTER. The utility will connect to the account on the remote host using the password you provided. It will then copy the contents of your ~/.ssh/id_rsa.pub key into a file in the remote account’s home ~/.ssh directory called authorized_keys.

      You should see the following output:

      Output

      Number of key(s) added: 1 Now try logging into the machine, with: "ssh 'username@203.0.113.1'" and check to make sure that only the key(s) you wanted were added.

      At this point, your id_rsa.pub key has been uploaded to the remote account. You can continue on to Step 3.

      Copying Public Key Using SSH

      If you do not have ssh-copy-id available, but you have password-based SSH access to an account on your server, you can upload your keys using a conventional SSH method.

      We can do this by using the cat command to read the contents of the public SSH key on our local computer and piping that through an SSH connection to the remote server.

      On the other side, we can make sure that the ~/.ssh directory exists and has the correct permissions under the account we’re using.

      We can then output the content we piped over into a file called authorized_keys within this directory. We’ll use the >> redirect symbol to append the content instead of overwriting it. This will let us add keys without destroying previously added keys.

      The full command looks like this:

      • cat ~/.ssh/id_rsa.pub | ssh username@remote_host "mkdir -p ~/.ssh && touch ~/.ssh/authorized_keys && chmod -R go= ~/.ssh && cat >> ~/.ssh/authorized_keys"

      You may see the following message:

      Output

      The authenticity of host '203.0.113.1 (203.0.113.1)' can't be established. ECDSA key fingerprint is fd:fd:d4:f9:77:fe:73:84:e1:55:00:ad:d6:6d:22:fe. Are you sure you want to continue connecting (yes/no)? yes

      This means that your local computer does not recognize the remote host. This will happen the first time you connect to a new host. Type “yes” and press ENTER to continue.

      Afterwards, you should be prompted to enter the remote user account password:

      Output

      username@203.0.113.1's password:

      After entering your password, the content of your id_rsa.pub key will be copied to the end of the authorized_keys file of the remote user’s account. Continue on to Step 3 if this was successful.

      Copying Public Key Manually

      If you do not have password-based SSH access to your server available, you will have to complete the above process manually.

      We will manually append the content of your id_rsa.pub file to the ~/.ssh/authorized_keys file on your remote machine.

      To display the content of your id_rsa.pub key, type this into your local computer:

      You will see the key’s content, which should look something like this:

      Output

      ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAABgQDgkLJ8d2gGEJCN7xdyVaDqk8qgeZqQ0MlfoPK3TzWI5dkG0WiZ16jrkiW/h6lhO9K1w89VDMnmNN9ULOWHrZMNs//Qyv/oN+FLIgK2CkKXRxTmbh/ZGnqOm3Zo2eU+QAmjb8hSsstQ3DiuGu8tbiWmsa3k3jKbWNWpXqY3Q88t+bM1DZrHwYzaIZ1BSA1ghqHCvIZqeP9IUL2l2DUfSCT9LXJEgMQhgjakJnzEGPgd5VHMR32rVrbIbbDzlyyoZ7SpCe5y0vYvbV2JKWI/8SEOmwehEHJ9RBZmciwc+1sdEcAJVMDujb9p5rX4hyvFpG0KGhZesB+/s7PdOa8zlIg4TZhXUHl4t1jpPC83Y9KEwS/Ni4dhaxlnr3T6l5hUX2cD+eWl1vVpogBqKNGBMrVR4dWs3Z4BVUf9exqTRRYOfgo0UckULqW5pmLW07JUuGo1kpFAxpDBPFWoPsg08CGRdEUS7ScRnMK1KdcH54kUZr0O88SZOsv9Zily/A5GyNM= demo@test

      Access your remote host using whichever method you have available.

      Once you have access to your account on the remote server, you should make sure the ~/.ssh directory exists. This command will create the directory if necessary, or do nothing if it already exists:

      Now, you can create or modify the authorized_keys file within this directory. You can add the contents of your id_rsa.pub file to the end of the authorized_keys file, creating it if necessary, using this command:

      • echo public_key_string >> ~/.ssh/authorized_keys

      In the above command, substitute the public_key_string with the output from the cat ~/.ssh/id_rsa.pub command that you executed on your local system. It should start with ssh-rsa AAAA....

      Finally, we’ll ensure that the ~/.ssh directory and authorized_keys file have the appropriate permissions set:

      This recursively removes all “group” and “other” permissions for the ~/.ssh/ directory.

      If you’re using the root account to set up keys for a user account, it’s also important that the ~/.ssh directory belongs to the user and not to root:

      • chown -R sammy:sammy ~/.ssh

      In this tutorial our user is named sammy but you should substitute the appropriate username into the above command.

      You can now attempt passwordless authentication with your Debian server.

      Step 3 — Authenticate to Debian Server Using SSH Keys

      If you have successfully completed one of the procedures above, you should be able to log into the remote host without the remote account’s password.

      The general process is the same:

      If this is your first time connecting to this host (if you used the last method above), you may see something like this:

      Output

      The authenticity of host '203.0.113.1 (203.0.113.1)' can't be established. ECDSA key fingerprint is fd:fd:d4:f9:77:fe:73:84:e1:55:00:ad:d6:6d:22:fe. Are you sure you want to continue connecting (yes/no)? yes

      This means that your local computer does not recognize the remote host. Type “yes” and then press ENTER to continue.

      If you did not supply a passphrase for your private key, you will be logged in immediately. If you supplied a passphrase for the private key when you created the key, you will be prompted to enter it now (note that your keystrokes will not display in the terminal session for security). After authenticating, a new shell session should open for you with the configured account on the Debian server.

      If key-based authentication was successful, continue on to learn how to further secure your system by disabling password authentication.

      Step 4 — Disable Password Authentication on your Server

      If you were able to log into your account using SSH without a password, you have successfully configured SSH-key-based authentication to your account. However, your password-based authentication mechanism is still active, meaning that your server is still exposed to brute-force attacks.

      Before completing the steps in this section, make sure that you either have SSH-key-based authentication configured for the root account on this server, or preferably, that you have SSH-key-based authentication configured for a non-root account on this server with sudo privileges. This step will lock down password-based logins, so ensuring that you will still be able to get administrative access is crucial.

      Once you’ve confirmed that your remote account has administrative privileges, log into your remote server with SSH keys, either as root or with an account with sudo privileges. Then, open up the SSH daemon’s configuration file:

      • sudo nano /etc/ssh/sshd_config

      Inside the file, search for a directive called PasswordAuthentication. This may be commented out. Uncomment the line and set the value to “no”. This will disable your ability to log in via SSH using account passwords:

      /etc/ssh/sshd_config

      ...
      PasswordAuthentication no
      ...
      

      Save and close the file when you are finished by pressing CTRL + X, then Y to confirm saving the file, and finally ENTER to exit nano. To actually implement these changes, we need to restart the sshd service:

      • sudo systemctl restart ssh

      As a precaution, open up a new terminal window and test that the SSH service is functioning correctly before closing this session:

      Once you have verified your SSH service, you can safely close all current server sessions.

      The SSH daemon on your Debian server now only responds to SSH keys. Password-based authentication has successfully been disabled.

      Conclusion

      You should now have SSH-key-based authentication configured on your server, allowing you to sign in without providing an account password.

      If you’d like to learn more about working with SSH, take a look at our SSH Essentials Guide.



      Source link

      How To Set Up SSH Keys on Rocky Linux 8


      Not using Rocky Linux 8?


      Choose a different version or distribution.

      Introduction

      SSH, or secure shell, is an encrypted protocol used to administer and communicate with servers. When working with a Rocky Linux server, chances are you will spend most of your time in a terminal session connected to your server through SSH.

      In this guide, we’ll focus on setting up SSH keys for a Rocky Linux 8 server. SSH keys provide a straightforward, secure method of logging into your server and are recommended for all users.

      Step 1 — Creating the RSA Key Pair

      The first step is to create a key pair on the client machine (usually your local computer):

      By default, ssh-keygen will create a 2048-bit RSA key pair, which is secure enough for most use cases (you may optionally pass in the -b 4096 flag to create a larger 4096-bit key).

      After entering the command, you should see the following prompt:

      Output

      Generating public/private rsa key pair. Enter file in which to save the key (/your_home/.ssh/id_rsa):

      Press ENTER to save the key pair into the .ssh/ subdirectory in your home directory, or specify an alternate path.

      If you had previously generated an SSH key pair, you may see the following prompt:

      Output

      /home/your_home/.ssh/id_rsa already exists. Overwrite (y/n)?

      If you choose to overwrite the key on disk, you will not be able to authenticate using the previous key anymore. Be very careful when selecting yes, as this is a destructive process that cannot be reversed.

      You should then see the following prompt:

      Output

      Enter passphrase (empty for no passphrase):

      Here you may optionally enter a secure passphrase, which is highly recommended. A passphrase adds an additional layer of security to your key, to prevent unauthorized users from logging in.

      You should then see the following output:

      Output

      Your identification has been saved in /your_home/.ssh/id_rsa. Your public key has been saved in /your_home/.ssh/id_rsa.pub. The key fingerprint is: a9:49:2e:2a:5e:33:3e:a9:de:4e:77:11:58:b6:90:26 username@remote_host The key's randomart image is: +--[ RSA 2048]----+ | ..o | | E o= . | | o. o | | .. | | ..S | | o o. | | =o.+. | |. =++.. | |o=++. | +-----------------+

      You now have a public and private key that you can use to authenticate. The next step is to get the public key onto your server so that you can use SSH-key-based authentication to log in.

      Step 2 — Copying the Public Key to Your Rocky Linux Server

      The quickest way to copy your public key to the Rocky Linux host is to use a utility called ssh-copy-id. This method is highly recommended if available. If you do not have ssh-copy-id available to you on your client machine, you may use one of the two alternate methods that follow (copying via password-based SSH, or manually copying the key).

      Copying your Public Key Using ssh-copy-id

      The ssh-copy-id tool is included by default in many operating systems, so you may have it available on your local system. For this method to work, you must already have password-based SSH access to your server.

      To use the utility, you need only specify the remote host that you would like to connect to and the user account that you have password SSH access to. This is the account to which your public SSH key will be copied:

      • ssh-copy-id username@remote_host

      You may see the following message:

      Output

      The authenticity of host '203.0.113.1 (203.0.113.1)' can't be established. ECDSA key fingerprint is fd:fd:d4:f9:77:fe:73:84:e1:55:00:ad:d6:6d:22:fe. Are you sure you want to continue connecting (yes/no)? yes

      This means that your local computer does not recognize the remote host. This will happen the first time you connect to a new host. Type yes and press ENTER to continue.

      Next, the utility will scan your local account for the id_rsa.pub key that we created earlier. When it finds the key, it will prompt you for the password of the remote user’s account:

      Output

      /usr/bin/ssh-copy-id: INFO: attempting to log in with the new key(s), to filter out any that are already installed /usr/bin/ssh-copy-id: INFO: 1 key(s) remain to be installed -- if you are prompted now it is to install the new keys username@203.0.113.1's password:

      Type in the password (your typing will not be displayed for security purposes) and press ENTER. The utility will connect to the account on the remote host using the password you provided. It will then copy the contents of your ~/.ssh/id_rsa.pub key into the remote account’s ~/.ssh/authorized_keys file.

      You should see the following output:

      Output

      Number of key(s) added: 1 Now try logging into the machine, with: "ssh 'username@203.0.113.1'" and check to make sure that only the key(s) you wanted were added.

      At this point, your id_rsa.pub key has been uploaded to the remote account. You can continue on to Step 3.

      Copying Public Key Using SSH

      If you do not have ssh-copy-id available, but you have password-based SSH access to an account on your server, you can upload your keys using a more conventional SSH method.

      We can do this by using the cat command to read the contents of the public SSH key on our local computer and piping that through an SSH connection to the remote server.

      On the other side, we can make sure that the ~/.ssh directory exists and has the correct permissions under the account we’re using.

      We can then output the content we piped over into a file called authorized_keys within this directory. We’ll use the >> redirect symbol to append the content instead of overwriting it. This will let us add keys without destroying any previously added keys.

      The full command looks like this:

      • cat ~/.ssh/id_rsa.pub | ssh username@remote_host "mkdir -p ~/.ssh && touch ~/.ssh/authorized_keys && chmod -R go= ~/.ssh && cat >> ~/.ssh/authorized_keys"

      You may see the following message:

      Output

      The authenticity of host '203.0.113.1 (203.0.113.1)' can't be established. ECDSA key fingerprint is fd:fd:d4:f9:77:fe:73:84:e1:55:00:ad:d6:6d:22:fe. Are you sure you want to continue connecting (yes/no)? yes

      This means that your local computer does not recognize the remote host. This will happen the first time you connect to a new host. Type yes and press ENTER to continue.

      Afterwards, you should be prompted to enter the remote user account password:

      Output

      username@203.0.113.1's password:

      After entering your password, the content of your id_rsa.pub key will be copied to the end of the authorized_keys file of the remote user’s account. Continue on to Step 3 if this was successful.

      Copying Public Key Manually

      If you do not have password-based SSH access to your server available, you will have to complete the above process manually.

      We will manually append the content of your id_rsa.pub file to the ~/.ssh/authorized_keys file on your remote machine.

      To display the content of your id_rsa.pub key, type this into your local computer:

      You will see the key’s content, which should look something like this:

      Output

      ssh-rsa AAAAB3NzaC1yc2EAAAADAQABAAACAQCqql6MzstZYh1TmWWv11q5O3pISj2ZFl9HgH1JLknLLx44+tXfJ7mIrKNxOOwxIxvcBF8PXSYvobFYEZjGIVCEAjrUzLiIxbyCoxVyle7Q+bqgZ8SeeM8wzytsY+dVGcBxF6N4JS+zVk5eMcV385gG3Y6ON3EG112n6d+SMXY0OEBIcO6x+PnUSGHrSgpBgX7Ks1r7xqFa7heJLLt2wWwkARptX7udSq05paBhcpB0pHtA1Rfz3K2B+ZVIpSDfki9UVKzT8JUmwW6NNzSgxUfQHGwnW7kj4jp4AT0VZk3ADw497M2G/12N0PPB5CnhHf7ovgy6nL1ikrygTKRFmNZISvAcywB9GVqNAVE+ZHDSCuURNsAInVzgYo9xgJDW8wUw2o8U77+xiFxgI5QSZX3Iq7YLMgeksaO4rBJEa54k8m5wEiEE1nUhLuJ0X/vh2xPff6SQ1BL/zkOhvJCACK6Vb15mDOeCSq54Cr7kvS46itMosi/uS66+PujOO+xt/2FWYepz6ZlN70bRly57Q06J+ZJoc9FfBCbCyYH7U/ASsmY095ywPsBo1XQ9PqhnN1/YOorJ068foQDNVpm146mUpILVxmq41Cj55YKHEazXGsdBIbXWhcrRf4G2fJLRcGUr9q8/lERo9oxRm5JFX6TCmj6kmiFqv+Ow9gI0x8GvaQ== sammy@host

      Log in to your remote host using whichever method you have available.

      Once you have access to your account on the remote server, you should make sure the ~/.ssh directory exists. This command will create the directory if necessary, or do nothing if it already exists:

      Now, you can create or modify the authorized_keys file within this directory. You can add the contents of your id_rsa.pub file to the end of the authorized_keys file, creating it if necessary, using this command:

      • echo public_key_string >> ~/.ssh/authorized_keys

      In the above command, substitute the public_key_string with the output from the cat ~/.ssh/id_rsa.pub command that you executed on your local system. It should start with ssh-rsa AAAA....

      Finally, we’ll ensure that the ~/.ssh directory and authorized_keys file have the appropriate permissions set:

      This recursively removes all “group” and “other” permissions for the ~/.ssh/ directory.

      If you’re using the root account to set up keys for a user account, it’s also important that the ~/.ssh directory belongs to the user and not to root:

      • chown -R sammy:sammy ~/.ssh

      In this tutorial our user is named sammy but you should substitute the appropriate username into the above command.

      We can now attempt key-based authentication with our Rocky Linux server.

      Step 3 — Logging In to Your Rocky Linux Server Using SSH Keys

      If you have successfully completed one of the procedures above, you should now be able to log into the remote host without the remote account’s password.

      The initial process is the same as with password-based authentication:

      If this is your first time connecting to this host (if you used the last method above), you may see something like this:

      Output

      The authenticity of host '203.0.113.1 (203.0.113.1)' can't be established. ECDSA key fingerprint is fd:fd:d4:f9:77:fe:73:84:e1:55:00:ad:d6:6d:22:fe. Are you sure you want to continue connecting (yes/no)? yes

      This means that your local computer does not recognize the remote host. Type yes and then press ENTER to continue.

      If you did not supply a passphrase when creating your key pair in step 1, you will be logged in immediately. If you supplied a passphrase you will be prompted to enter it now. After authenticating, a new shell session should open for you with the configured account on the Rocky Linux server.

      If key-based authentication was successful, continue on to learn how to further secure your system by disabling your SSH server’s password-based authentication.

      Step 4 — Disabling Password Authentication on your Server

      If you were able to log in to your account using SSH without a password, you have successfully configured SSH-key-based authentication to your account. However, your password-based authentication mechanism is still active, meaning that your server is still exposed to brute-force attacks.

      Before completing the steps in this section, make sure that you either have SSH-key-based authentication configured for the root account on this server, or preferably, that you have SSH-key-based authentication configured for a non-root account on this server with sudo privileges. This step will lock down password-based logins, so ensuring that you will still be able to get administrative access is crucial.

      Once you’ve confirmed that your remote account has administrative privileges, log into your remote server with SSH keys, either as root or with an account with sudo privileges. Then, open up the SSH daemon’s configuration file:

      • sudo vi /etc/ssh/sshd_config

      Inside the file, search for a directive called PasswordAuthentication. This may be commented out with a # hash. Press i to put vi into insertion mode, and then uncomment the line and set the value to no. This will disable your ability to log in via SSH using account passwords:

      /etc/ssh/sshd_config

      ...
      PasswordAuthentication no
      ...
      

      When you are finished making changes, press ESC and then :wq to write the changes to the file and quit. To actually implement these changes, we need to restart the sshd service:

      • sudo systemctl restart sshd

      As a precaution, open up a new terminal window and test that the SSH service is functioning correctly before closing your current session:

      Once you have verified your SSH service is still working properly, you can safely close all current server sessions.

      The SSH daemon on your Rocky Linux server now only responds to SSH keys. Password-based authentication has successfully been disabled.

      Conclusion

      You should now have SSH-key-based authentication configured on your server, allowing you to sign in without providing an account password.

      If you’d like to learn more about working with SSH, take a look at our SSH Essentials Guide.



      Source link

      How To Use Foreign Keys in SQL


      The author selected Apache Software Foundation to receive a donation as part of the Write for DOnations program.

      Introduction

      When working on a large SQL project, you must maintain the accuracy and consistency of data across all tables with foreign keys. A foreign key is a column or group of columns in a relational database table that provides a link between data in two tables. In this use case, this is where referential integrity comes into play. 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.

      Another 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.

      Referential integrity ensures all data references are valid and prevents inconsistent entries or orphaned records. Referential integrity is also useful for preventing entries of invalid data in a multi-user database environment.

      In this guide, you’ll enforce referential integrity with foreign keys on your database. Although this guide is tested on a MySQL database, it can still work in other SQL-based databases with just a few syntax changes.

      Prerequisites

      To complete this tutorial, you’ll require the following:

      Step 1 — Setting Up a Sample Database and Tables

      In this step, you’ll create a sample database and set up a few tables. You’ll also insert some sample data that you’ll use to work with foreign keys throughout the guide.

      Begin by connecting to your server as a non-root user. Then, execute the following command to log in to your MySQL server. Replace example_user with the exact name for your non-root account.

      • sudo mysql -u example_user -p

      When prompted, enter the non-root user account password of your MySQL server and press ENTER or RETURN to proceed. Next, issue the following SQL command to create a sample company_db database:

      • CREATE DATABASE company_db;

      Confirm the following output to make sure the database has been created without errors.

      Output

      Query OK, 1 row affected (0.01 sec)

      Once you’ve successfully created the database with no error messages in your output, apply the SQL USE keyword to switch to your new company_db database:

      You should see the following confirmation that shows you’ve successfully switched to the company_db database:

      Output

      Database changed

      Next, set up a job_titles table using the CREATE TABLE command. This table works as a look-up table for all job titles available in your database. 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. You’re using the AUTO_INCREMENT keyword to let MySQL automatically assign sequential numeric values every time you insert a new job title.

      In the CREATE TABLE command, include a job_title_name column that stores a human-readable value for a job title. This column stores string values with the maximum length at 50 characters. You will define this data type with the syntax VARCHAR(50).

      Following CREATE TABLE command, instruct MySQL to use the InnoDB database engine by including the ENGINE = InnoDB keyword. This is a transaction-ready general-purpose storage engine that handles concurrency while still ensuring high reliability and high performance in your database application.

      Execute the following command to create the job_titles table:

      • CREATE TABLE job_titles (
      • job_title_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      • job_title_name VARCHAR(50)
      • ) ENGINE = InnoDB;

      After running the CREATE TABLE job_titles... statement, make sure your command was completed successfully by confirming the following output:

      Output

      Query OK, 0 rows affected (0.03 sec)

      You now have a look up table for all valid positions available in your example company. Next, insert some sample positions into the job_titles table:

      • INSERT INTO job_titles (job_title_name) VALUES ('BRANCH MANAGER');
      • INSERT INTO job_titles (job_title_name) VALUES ('CLERK');
      • INSERT INTO job_titles (job_title_name) VALUES ('LEVEL 1 SUPERVISOR');

      After each command, you should get the following confirmation message:

      Output

      Query OK, 1 row affected (0.00 sec) ...

      Now that you’ve inserted the available job titles, use the MySQL SELECT keyword to query the job_titles table to verify your data:

      • SELECT
      • job_title_id,
      • job_title_name
      • FROM job_titles;

      You should now see a list of all available positions listed as shown below:

      Output

      +--------------+--------------------+ | job_title_id | job_title_name | +--------------+--------------------+ | 1 | BRANCH MANAGER | | 2 | CLERK | | 3 | LEVEL 1 SUPERVISOR | +--------------+--------------------+ 3 rows in set (0.00 sec)

      Next, create an employees table. This table holds records for all staff members in the company. The job_title_id column in the employees table points back to the same column in the job_titles table. You’re achieving this by issuing the statement FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id). For consistency purposes, you’re using the BIGINT data type, which you used for the related columns.

      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.

      You’re capturing the employees’ names using the first_name and last_name text fields with a maximum length of 50 characters. This data type is also perfect for the phone number. Therefore, a VARCHAR(50) data type should work for the first_name, last_name, and phone fields.

      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. Again, make sure to include the keyword ENGINE = InnoDB to take advantage of the InnoDB storage engine as outlined in Step 1.

      To create the employees table, run the following command:

      • CREATE TABLE employees (
      • employee_id BIGINT NOT NULL AUTO_INCREMENT PRIMARY KEY,
      • job_title_id BIGINT NOT NULL,
      • first_name VARCHAR(50),
      • last_name VARCHAR(50),
      • phone VARCHAR(50),
      • INDEX (job_title_id),
      • FOREIGN KEY (job_title_id) REFERENCES job_titles (job_title_id)
      • ) ENGINE = InnoDB;

      Ensure you get the following output confirming you’ve created the table:

      Output

      Query OK, 0 rows affected (0.04 sec)

      Now that you’ve set up the right database and tables for testing purposes, you’ll now look at what occurs next when inserting data into the table.

      Step 2 — Inserting Invalid Data

      In this step, you’ll insert some orphaned records into the employees table. Orphaned records in this case are records with invalid job_title_ids. From your job_titles table, you only have 3 valid job titles as shown below.

      1. BRANCH MANAGER
      2. CLERK
      3. LEVEL 1 SUPERVISOR

      Now, attempt adding some invalid records into the employees table by running the following INSERT statements:

      • INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (4, 'JOHN', 'DOE', '11111');
      • INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (15, 'MARY', 'SMITH', '22222');
      • INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (7, 'JANE', 'MIKE', '33333');

      The above INSERT statements should all fail and display the following errors since 4, 15, and 7 are invalid job_title_ids.

      Output

      ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`)) ...

      In the next step, you’ll enter valid data into the employees table and check if the commands succeed.

      Step 3 — Inserting Valid Data

      You’ve seen how referential integrity prevents the entry of invalid data when tables are interlinked with foreign keys. In other words, using foreign keys keeps your database in a consistent state even without necessarily coding that business logic in an external client application.

      In this step, you’ll now insert valid data and see if the inserts will be successful. Run the following commands:

      • INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'PETER', 'SMITH', '55555');
      • INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (1, 'JOHN', 'DOE', '11111');
      • INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'STEVE', 'KIM', '66666');
      • INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (3, 'MARY', 'ROE', '22222');
      • INSERT INTO employees (job_title_id, first_name, last_name, phone) VALUES (2, 'JANE', 'MIKE', '33333');

      As you’re inserting valid job_title_ids, the INSERT statements will now succeed. After executing each INSERT command, you’ll receive the following output:

      Output

      Query OK, 1 row affected (0.00 sec) ...

      By now, you’ll notice that implementing referential integrity is a useful approach for validating data and preventing the entry of non-existent records when working with interlinked tables. As well, by using foreign keys, you’re creating an optimized database that allows you to query interlinked data in an efficient manner.

      For instance, to retrieve all staff members’ records with the position names spelled out, run the following JOIN statement against the employees and job_titles tables.

      • SELECT
      • employee_id,
      • employees.job_title_id,
      • job_titles.job_title_name,
      • first_name,
      • last_name,
      • phone
      • FROM employees
      • LEFT JOIN job_titles
      • ON employees.job_title_id = job_titles.job_title_id;

      In the output below, each employee’s information is now displayed alongside their associated role/position:

      Output

      +-------------+--------------+--------------------+------------+-----------+-------+ | employee_id | job_title_id | job_title_name | first_name | last_name | phone | +-------------+--------------+--------------------+------------+-----------+-------+ | 5 | 1 | BRANCH MANAGER | JOHN | DOE | 11111 | | 4 | 2 | CLERK | PETER | SMITH | 55555 | | 6 | 2 | CLERK | STEVE | KIM | 66666 | | 8 | 2 | CLERK | JANE | MIKE | 33333 | | 7 | 3 | LEVEL 1 SUPERVISOR | MARY | ROE | 22222 | +-------------+--------------+--------------------+------------+-----------+-------+ 5 rows in set (0.00 sec)

      As you can see in the output above, you have one BRANCH MANAGER, three of CLERK, and one LEVEL 1 SUPERVISOR.

      Foreign keys are also great when it comes to preventing the deletion of parent records that are already referenced by a foreign key in a linked child table. Here are a few real-life examples where you can apply this:

      • In an e-commerce website, you can prevent accidental deletion of customer’s details from a customers table when you’ve active orders for the customer in the sales table.

      • In a library system, you can prevent deleting a student from a registers table when the student has associated records in the issued_books table.

      • In a bank, you can use the foreign keys approach to avoid deleting records from the savings_accounts table when a client has already made some deposits/withdrawals in the savings_accounts_transactions table.

      Similarly, you can attempt to delete data in your table. On your command-line terminal, delete a single position from the job_titles table:

      • DELETE FROM job_titles
      • WHERE job_title_id = 1 ;

      Since you’ve already inserted a record in the employees table with a title of a BRANCH MANAGER, the DELETE statement will fail and display the following error:

      Output

      ERROR 1451 (23000): Cannot delete or update a parent row: a foreign key constraint fails (`company_db`.`employees`, CONSTRAINT `employees_ibfk_1` FOREIGN KEY (`job_title_id`) REFERENCES `job_titles` (`job_title_id`))

      Again, add a new role into the job_titles table:

      • INSERT INTO job_titles (job_title_name) VALUES ('CEO');

      You should receive the following output once you’ve executed the command successfully.

      Output

      Query OK, 1 row affected (0.00 sec) ...

      Again, query the job_titles table to check the job_title_id of the new position:

      • SELECT
      • job_title_id,
      • job_title_name
      • FROM job_titles;

      You should now see a list of all available positions listed as shown below. The CEO role has a job_title_id of 4:

      Output

      +--------------+--------------------+ | job_title_id | job_title_name | +--------------+--------------------+ | 1 | BRANCH MANAGER | | 2 | CLERK | | 3 | LEVEL 1 SUPERVISOR | | 4 | CEO | +--------------+--------------------+ 4 rows in set (0.00 sec)

      You now have 4 rows in the table. Next, delete the new role with job_title_id of 4 before entering any associated record into the employees table.

      • DELETE FROM job_titles
      • WHERE job_title_id = 4 ;

      The DELETE statement should now succeed.

      Output

      Query OK, 1 row affected (0.00 sec)

      After completing all the above tests without any errors, it is now clear that your foreign keys are working as expected.

      Conclusion

      In this guide, you’ve set up a sample database with interlinked tables and practiced the use of referential integrity in a relational database management system. You’ve seen how foreign keys are important in validating and preventing the deletion of data that would otherwise put the database in an inconsistent state. Use the knowledge in this guide towards your next database project to take advantage of foreign keys.

      For more practice with your MySQL database, check out these tutorials:



      Source link