Summary

Today you learned how to create tables and views. Views provide an alternate way of looking at data in a table. Each column in a table requires a name, a data type, and a nullability status, which may be null, not null, or identity. Default constraints, when defined on a column, provide a value when an explicit value is not defined during an INSERT. Identity columns provide a server-maintained, unique, ascending numeric key for easy primary key creation, and a simple way to uniquely identify rows in a table.

You learned how to modify data in a table by using the INSERT, UPDATE, and DELETE commands.

INSERT... SELECT and SELECT... INTO copy rows from one table to another. The first requires the destination table to exist and is logged; the last creates the destination table and is not logged.

TRUNCATE TABLE removes all rows from a table, and resets its identity counter.

You learned how to apply declarative referential integrity through the use of three types of constraints. Primary key constraints require unique values in all rows for the key. Foreign key constraints require that the value exist in the primary key column. Unique constraints require unique values, like PK constraints, but allow inclusion of null columns in the constraint and a single null row.

Q&A

Q If I’ve already created a table, can I add DRI constraints to it without dropping and re-creating it? Can I do this if it has data?

A Yes. You may use the ALTER TABLE command to accomplish this. ALTER TABLE will also allow you to add null columns to a table after the table has been defined. To add a PK constraint to the authors table, use this syntax:

alter table authors add constraint PK_NC_au_id
    primary key (au_id)

Q Can I insert new identity values explicitly?

A If you own the table, you can set identity_insert on. This allows duplicate identity values, however, so be careful that you do not introduce duplicates. This is helpful when migrating an existing table to a new server, or if you want to move a row in the table to a different identifier value that had been skipped.

Q Can I create a table with just a single identity column, and nothing else?

A Strangely, yes, but you cannot insert data into it unless you set identity_insert on, defeating the purpose of the identity column. This is because you cannot provide an empty column list to insert.

Q Can I create a null identity column?

A No.

Workshop

The Workshop provides quiz questions to help you solidify your understanding of the material covered, as well as exercises to provide you with experience in using what you’ve learned. Try to understand the quiz and exercise answers before continuing on to the next day’s lesson. Answers are provided in the appendix.

Quiz

1.  How many identity columns can you define in a table?

2.  In the following table definition, what is the maximum number of rows that could be inserted into the table?

create table employer(
     emp_id numeric(5,0) identity(0,1)
     emp_address varchar
)

3.  If a view is defined on a subset of columns in a table, are you allowed to delete from that view?

4.  How many defaults can be defined on a column?

Exercises

1.  Create a table called customer with the following columns: cust_id, an identity column starting with the number 1000; name, a required field for the customer’s company name; email, an optional field for the e-mail address; and a preferred column that allows either a TRUE or FALSE value to indicate whether the customer is a preferred customer. The preferred column defaults to FALSE when a value is not provided.

2.  Insert this data into your table:

Name	Email	Preferred?
OK Office Products	Larry@OK.com	Yes
German Imports, Inc.
ACME Cartoon Demolition	roadrunner@wb.com	No
Red Rum		No
Elmo’s Used Cars	redfurry@tickled.net	Yes

3.  Review: Using the tables created in the DRI example, create a query that shows the retail dollar value of the current inventory. Create another query that totals sales, excluding tax.