T-Sql

A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

For example, say we have two tables, a CUSTOMER table that includes all customer data, and an ORDER table that includes all customer orders. The constraint here is that all orders must be associated with a customer that is already in the CUSTOMER table. In this case, we will place a foreign key on the ORDERS table and have it relate to the primary key of the CUSTOMER table. This way, we can ensure that all orders in the ORDERS table are related to a customer in the CUSTOMER table. In other words, the ORDERS table cannot contain information on a customer that is not in the CUSTOMER table.

Sometimes we may decide that we need to get rid of a table in the database for some reason. In fact, it would be problematic if we cannot do so because this could create a maintenance nightmare for the DBAs. Fortunately, SQL allows us to do it, as we can use the DROP TABLE command. The syntax for DROP TABLE is

DROP TABLE "table_name"

So, if we wanted to drop the table called a customer that we created in the last section, we simply type

Sometimes we wish to get rid of all the data in a table. One way of doing this is with DROP TABLE, which we saw in the last section. But what if we wish to simply get rid of the data but not the table itself? For this, we can use TRUNCATE TABLE command. The syntax for TRUNCATE TABLE is

TRUNCATE TABLE "table_name"

So, if we wanted to truncate the table called a customer that we created in SQL CREATE, we simply type

In the previous sections, we have seen how to retrieve information from tables. But how do these rows of data get into these tables in the first place? This is what this section, covering the INSERT statement, and the next section, covering the UPDATE statement, are about.

There are essentially basically two ways to INSERT data into a table. One is to insert it one row at a time, and the other is to insert multiple rows at a time. Let’s first look at how we may INSERT data one row at a time:

Once we have the information in the table, we might find that there is a need to modify the data. To do so, we can use the UPDATE command. The syntax for this is

UPDATE “table_name”
SET “column_1” = [new value]
WHERE {condition}

It is easiest to use an example. Say we currently have a table as below:

Table Store_Information

We find that the sales for Los Angeles on 01/08/1999 is actually $500 instead of $300, and we need to update that particular entry. To do so, we use the following SQL: