Chapter 10. Managing Data

In the previous chapter, we learned not only how to create a database but how to do it well. Well-considered table design, column constraints, and relationships will really shine once we start putting data into the tables. With our strong table design and the well-thought-out relationships between them, we will be able to join efficiently and easily. When a piece of data needs to be changed (e.g., a customer address), we only need to change it in one place rather than several. When a bad piece of data comes in, hopefully we have set enough sensible constraints to prevent it from entering the database.

In this chapter, we will learn how to INSERT, DELETE, and UPDATE records. Fortunately, writing operations like these is much simpler than writing SELECT statements.

These SQL write operations do not have to be done by a human. Software (written in Java, Python, or other coding languages) will often generate and execute SQL statements to read and write data the same way a human would, but much more efficiently. Although this is beyond the scope of this book, coding languages will be touched on in the next chapter if that is pertinent to you.

INSERT

In a relational database, data only exists if the database first receives records. The INSERT statement does just that and inserts a record into the database. You can pick what fields to populate in the record, and the rest of the fields will be null or use a default value.

First, we will INSERT an ATTENDEE record into the SurgeTech database we created in the last chapter. An INSERT to add yourself to the database should look something like this. Execute the following statement with your name:

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME)
VALUES ('Thomas','Nield')

Let’s break this statement down:

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME)
VALUES ('Thomas','Nield')

To start, we declare that we are inserting a record into the ATTENDEE table, and the fields we are choosing to populate are FIRST_NAME and LAST_NAME:

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME)
VALUES ('Thomas','Nield')

Then we specify the values for each of these fields. Note that we specify the values in the same order we declared the fields in: 'Thomas' corresponds to FIRST_NAME, and 'Nield' to LAST_NAME.

Run SELECT * FROM ATTENDEE to see if our INSERT made it in. Sure enough, the record now exists (Figure 10-1).

Our newly inserted record in the ATTENDEE table
Figure 10-1. Our newly inserted record in the ATTENDEE table

There are a number of observations to make here. We did not populate all the columns in our INSERT, but due to the rules we created in the previous chapter, some of the columns were assigned a default value.

The ATTENDEE_ID gave itself a value of 1 due to our PRIMARY KEY and AUTOINCREMENT rule.  If we were to INSERT another record, it would automatically be assigned an ATTENDEE_ID of 2, then 3, and so on. On an INSERT, you should avoid populating the ATTENDEE_ID field yourself and let it assign its own ID.

Note

Again, the AUTOINCREMENT constraint in SQLite is actually not necessary. It is needed for MySQL and other platforms, though, hence why we are doing it for practice. In SQLite, simply making a column of type INTEGER a primary key will automatically assign IDs to new records.

PHONE and EMAIL were not specified in our INSERT, so they were left null. If either of these columns had a NOT NULL constraint and no default value policy, our INSERT would have failed. But in our design, we have allowed these two fields to be null in case our attendees prefer to be off the grid.

The VIP status was not specified in our INSERT either, but we gave this field a default value of false (0). So instead of making it null, SQLite resorted to using the default value we specified.

Hopefully by now, you are already appreciating that the design is working efficiently. Because of the policies we set, the columns resorted to default values when they were not provided with any.

Multiple INSERTs

If you have a lot of records to INSERT, you do not have to do it one at a time. You can specify multiple records in a single INSERT command. Simply repeat the clause following VALUES and separate each entry with a comma:

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME, PHONE, EMAIL, VIP)
VALUES
('Jon', 'Skeeter',4802185842,'[email protected]', 1),
('Sam','Scala',2156783401,'[email protected]', 0),
('Brittany','Fisher',5932857296,'[email protected]', 0)

Doing multiple inserts in this manner is far more efficient, especially if you have thousands of records. If a process written in Java or Python is populating a table, it should use this syntax to insert large amounts of records rather than executing one INSERT at a time. Otherwise, the process can run very slowly.

You can also INSERT records using the results from a SELECT query. This is helpful if you need to migrate data from one table to another. Just make sure the SELECT fields line up with the INSERT fields and have the same order and data types:

INSERT INTO ATTENDEE (FIRST_NAME, LAST_NAME, PHONE, EMAIL)
SELECT FIRST_NAME, LAST_NAME, PHONE, EMAIL
FROM SOME_OTHER_TABLE

Testing the Foreign Keys

Let’s take an opportunity to witness another policy of our design at work: the foreign keys.

Right now, we should only have four attendees with ATTENDEE_ID assignments of 1, 2, 3, and 4. But test this functionality by inserting a COMPANY record with a PRIMARY_CONTACT_ID value of 5:

INSERT INTO COMPANY (NAME, DESCRIPTION, PRIMARY_CONTACT_ID)
VALUES ('RexApp Solutions', 'A mobile app delivery service', 5)

This query should have failed and an error should be displayed at the bottom of the window (Figure 10-2).

The Foreign Key constraint successfully raised an error
Figure 10-2. The foreign key constraint successfully raised an error

This is good because it means our foreign key constraint has worked: it kept an orphan record out. The INSERT needs to have a PRIMARY_CONTACT_ID that is existent. So, if we change it to 3 (Sam Scala), the INSERT should now work correctly:

INSERT INTO COMPANY (NAME, DESCRIPTION, PRIMARY_CONTACT_ID)
VALUES ('RexApp Solutions', 'A mobile app delivery service', 3)

DELETE

The DELETE statement is dangerously simple. It deletes all records in a table:

DELETE FROM ATTENDEE

However, you can conditionally delete records with a WHERE statement. If we wanted to remove all records that have no contact information, we could filter to records where PHONE and EMAIL are null:

DELETE FROM ATTENDEE
WHERE PHONE IS NULL
AND EMAIL IS NULL

Because it is perilously easy to make mistakes with a DELETE statement, it is a good practice to replace the DELETE with a SELECT * first. Executing that query gives us a preview of what records will be deleted:

SELECT * FROM ATTENDEE
WHERE PHONE IS NULL
AND EMAIL IS NULL

TRUNCATE TABLE

In the previous section, we looked at a means to delete all records from a table:

DELETE FROM ATTENDEE

Although not used in SQLite, on some database platforms (like MySQL) the preferred way to delete all records from a table is to use TRUNCATE TABLE:

TRUNCATE TABLE ATTENDEE

Using this command will allow the database engine to reset the autoincrements for any primary keys as well as any other constraint behaviors. It also allows it to make some optimizations behind the scenes to reset the table.

While SQLite does not support TRUNCATE TABLE, it does allow some similar optimizations when you run a DELETE without a WHERE.

UPDATE

Finally, we come to the UPDATE command. The UPDATE modifies existing records. If we wanted to update the EMAIL values for all records to be uppercase, we could do that with this statement using the UPPER() function:

UPDATE ATTENDEE SET EMAIL = UPPER(EMAIL)

We can also update multiple fields at once. Just separate each expression after the SET keyword with a comma. To update both the FIRST_NAME and LAST_NAME fields to uppercase, run this command:

UPDATE ATTENDEE SET FIRST_NAME = UPPER(FIRST_NAME),
LAST_NAME = UPPER(LAST_NAME)

Like with DELETE, we can use a WHERE to conditionally apply updates to records. Execute the following query to set the VIP field to true where the ATTENDEE_ID is 3 or 4:

UPDATE ATTENDEE SET VIP = 1
WHERE ATTENDEE_ID IN (3,4)

DROP TABLE

There may be times where you want to remove a table altogether from the database. Just type DROP TABLE followed by the name of the table you want to delete (this is a dangerous statement as well because it deletes the table permanently, so be careful and certain about what you are doing):

DROP TABLE MY_UNWANTED_TABLE

Summary

At this point, you have the tools you need to go out and create your own database and manage its data. You may have questions on how to do all of this efficiently or provide practical means for users to input and update data, because chances are you cannot teach all of them SQL and they will want a graphical user interface. Or maybe you want to know how to pump large amounts of data into a database automatically or synchronize it with another data source. This will be lightly addressed in the final chapter.

..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset
18.224.64.89