Previous Table of Contents Next


Week 2

Day 9
INSERT, UPDATE, and DELETE

The previous eight days taught you how to use SELECTto retrieve data from tables in a lot of different ways. Today,you’ll learn how to create tables and manipulate the data inLaterthem. First, I’ll talk about creating tables and thefollowing related topics:

•  CREATE TABLE syntax
•  null, not null, and identity columns
•  Applying user-defined data types in tables
•  Defining default column values (default constraints)
•  TRUNCATE TABLE and DROP TABLE

Later today, I’ll talk about data manipulation using thefollowing statements:

•  INSERT
•  UPDATE
•  DELETE

To finish up today, as I promised on Day 7, I’ll discussdeclarative referential integrity.

Creating Tables

When you create a table, you must define some attributes foreach column in the table. First, you define the data type for thecolumn. In the titles table, there is a title column of typevarchar and a price column of type smallmoney. If you execute ansp_help on the titles table, you’ll see the datatypes for each column.

Second, you specify a length for data types that require it.Data types such as integer, smallinteger, and money have fixedlengths, so you cannot specify a length for them. Character fieldsand variable length fields (varbinary, varchar) need to know howmany characters to hold. This is specified in parentheses after thedata type, as you will see in the first example.

Last, you specify whether or not the column will accept nullvalues. If you want to allow a row of data to be inserted withoutspecifying values for some of the columns, null can be allowed inthe nonessential columns. For instance, in the authors table, theauthor id is required (it is not null), but the address is optional(it is null). An author may have written books without giving anaddress. Maybe he travels abroad, or lives in a tent somewhere.(You’ll notice that phone is defined as not null. There hasto be a way to get in touch with an author. Maybe he lives in atent with a cell phone.)

Note:  The customer table is a made-up table. It is notpart of the pubs database.

Here is an example of a table creation: 

create table customer(
     cust_id int not null,
     fname varchar(30) not null,
     lname varchar(30) not null,
     addr1 varchar(40) null,
     addr2 varchar(40) null,
     city varchar(40) null,
     zip  char(9) null
     )

There are seven columns in the new customer table. Afterthe table is created, to add a new row to the customer table, Ineed to provide only a cust_id, fname, and lname. The other columnsare defined as null, and thus are optional.

Each column definition is separated by a comma. Columndefinitions have three parts. The first part is the column name,which can be up to 30 characters long, and which cannot containspaces or punctuation. Column names, like table names (and, infact, all object names) are case-sensitive. If you define yourtable with upper- and lowercase letters, you have to refer to itwith those same upper- and lowercase letters.

NOTE:  Style Guide: Creating tables

In this book, when I create tables, I’ll list the tablename first, and each column on a separate line. Here are a fewalternate formatting choices.

Line up each of the three parts of each column’sdefinition, for example:

create table customer(
       cust_id  int          not null,
       fname    varchar(30)  not null,
       lname    varchar(30)  not null,
       addr1    varchar(40)  null,
       addr2    varchar(40)  null,
       city     varchar(40)  null,
       zip      char(9)      null
       )

The most common formatting style is to list each column flushleft, aligned with the CREATE statement, and keep the lastparenthesis with the last column:

create table customer(
cust_id int not null,
fname varchar(30) not null,
lname varchar(30) not null,
addr1 varchar(40) null,
addr2 varchar(40) null,
city varchar(40) null,
zip char(9)   null)

You could also list all the columns in a long line, starting anew line only when you run out of space. I use this format when Icreate tables that won’t be around for long, if I’mtesting something out. It’s easier to type, and since I planto drop it soon anyway, the format doesn’t have to be pretty.

I think I like the first style best because it reminds me of C programming. In a long list of table creation scripts, that stylemakes it easier to find where each table begins and ends. A commonsyntax error during table creation is to add a comma to the lastcolumn, and this style helps me to keep from doing that.

The second part of the column definition is the data type. Eachcolumn can have only one data type. On Day 2, I presented a listof data types and when each type was used. Character (and varchar)types store names, addresses, and titles. Money columns store moneyvalues, integers store numbers, and so on.

Last, the null status of the column is defined. This can be oneof three possible types: null, not null, or identity. Identitycolumns provide a unique number for every row in the table, and aredescribed in detail a little later. Null columns allow null valuesto be inserted in lieu of a known value. If a column is defined asnot null, an error results if no data is provided during anINSERT.

TIP:  To be (null) or not to be…
When considering whether to allow null values, ask yourselfthese questions:
•  Does the other data in the row make sense without datain this column? For instance, in the authors table, would an authorwith no au_id be useful? (It wouldn’t, because itcouldn’t be related to data in any other tables. This is whyau_id is not a null column.)
•  Could this column accept a null value initially and bechanged later? A customer without an address can’t get aninvoice, so going by the first rule, you might be inclined to makeall the address columns not null. But a customer could place anorder and provide address information some time before receivinghis merchandise. If other data in the row could be provided withoutthis column, consider allowing nulls.
•  Is a null worth the storage overhead? The first nullcolumn in a table costs five extra bytes per row. More null columnscost one extra byte each. Variable length columns are alreadyincurring this overhead, so extra storage does not apply to them.If it isn’t worth the storage overhead, consider a singlevalue (N/A for a character column, perhaps a -1for a numeric column that wouldn’t normally be negative, andso on) that indicates no data. This tip won’t be appreciatedby folks who stick by stringent database design rules, but in thereal world, decisions are made based on performance issues as wellas style and code purity issues.

TIP:  All data types except the bit may be null. A bit canonly be 0 or 1. Bits are useful data types whenyou need a column that can be only true or false. If there aremultiple bit fields in a table, the server employs a bit-packingstrategy to save space. This allows up to 16-bit columns in asingle byte pair.


Previous Table of Contents Next
Используются технологии uCoz