SQL Database Design

Here's a continuation of my review of Getting Started with SQL, an O'Reilly guide by Thomas Nield, last blogged about on December 23, 2016.

Chapter 9 on Database Design begins with some background information. It notes that SQL Injection is a way to use a SELECT statement to query private data in a web site that has inadequate security measures.

The primary focus of this section of the guide is primary keys and foreign keys. Primary keys are unique identifiers in a parent table, but foreign keys are identifiers in a child table that point back to the primary key. So the foreign key has to be a value in the primary key field.

In order to create your own database simply go to the Database menu and select . . . Add a Database. Save the new database with the extension '.db'.

Double-clicking on the new database will show that it is set-up with two sections - Tables and Views. Right click on the tables inside the new database and select 'Create a table'.

To add a new column for the table click on the icon on the toolbar to the right of the X. As you can you have the option to set a field as a primary key in the check boxes below where you enter the name. Click on configure and you'll get the option to make the field auto increment.

You also have the option to set a new column as VARCHAR - which allows you to enter a set maximum length for the column. Check of 'Not Null' forces a value to be entered in this field.

After you have entered the needed columns, when you click on the green check box on the toolbar the table is saved, and a query statement is generated. If you copy this code and paste into the SQL Editor in the Tools menu, SQLiteStudio will automatically generate a copy of the table.

In order to designate a column as a foreign key double-click on it and then select the check box for 'Foreign Key'.

. . . when you select Configure to the right of the 'Foreign Key' a new dialog box will appear and lets you select the table and column that has the primary key that you refer back to.

A column in one table is a foreign key when it references back to the primary key in another table, and can only use values entered in that column in the parent table. After this is done, you need to the click the green check box on the toolbar to commit the changes to the structure of the table.

You can use query statements to create saved views in SQLiteStudio. Just right click on View and select 'Create a view'. Paste in the query code

. . . and you'll be able to bring up a view that displays specific columns in a table.