User Guide

Table Of Contents
452 Chapter 19: Introduction to Databases and SQL
A bank might use a transaction to encapsulate a transfer from one account to another. For
example, if you transfer money from your savings account to your checking account, you do not
want the bank to debit the balance of your savings account unless it also credits your checking
account. If the update to the checking account fails, the bank can rollback the debit of the savings
account as part of the transaction.
ColdFusion MX includes the
cftransaction tag that lets you implement database transactions
for controlling rollback and commit. For more information, see CFML Reference.
Database design guidelines
From this basic description, the following database design rules emerge:
Each record should contain a unique identifier as the primary key such as an employee ID, a
part number, or a customer number. The primary key is typically the column used to maintain
each record's unique identity among the tables in a relational database. Databases allow you to
use multiple columns for the primary key.
When you define a column, you define a SQL data type for the column, such as allowing only
numeric values to be entered in the salary column.
Assessing user needs and incorporating those needs in the database design is essential to a
successful implementation. A well-designed database accommodates the changing data needs
within an organization.
The best way to familiarize yourself with the capabilities of your database product or database
management system (DBMS) is to review the product documentation.
Using SQL
This section introduces SQL, describes basic SQL syntax, and contains examples of SQL
statements. It provides enough information for you to begin using ColdFusion MX. However,
this section does not contain an exhaustive description of the entire SQL programming language.
For complete SQL information, see the SQL reference that ships with your database.
A query is a request to a database. The query can ask for information from the database, write
new data to the database, update existing information in the database, or delete records from
the database.
Structured Query Language (SQL) is an ANSI/ISO standard programming language for writing
database queries. All databases supported by ColdFusion support SQL, and all ColdFusion tags
that access a database allow you to pass SQL statements to the tag.
SQL example
The most commonly used SQL statement in ColdFusion is the SELECT statement. The
SELECT statement reads data from a database and returns it to ColdFusion. For example, the
following SQL statement reads all the records from the employees table:
SELECT * FROM employees
You interpret this statement as "Select all rows from the table employees" where the wildcard
symbol (*) corresponds to all columns.