The invoice detail table will have foreign key references to the item table and to the invoices table. First, I’ll define the table and then worry about the primary key:

create table inv_detail(
    inv_id int not null constraint FK_NC_detail_inv_id
        foreign key references invoices,
    item_id smallint not null constraint FK_NC_detail_item_id
        foreign key references item,
    qty smallint not null default 1
    )

Does this table require a primary key? No—no foreign keys reference the detail table. Is a primary key useful? Perhaps. No single column in this table can guarantee uniqueness. An inv_id will appear many times, once for each type of item ordered. An item_id will appear many times, if the same widget is ordered by different people or at different times. However, the combination of these two columns should be unique: no item should appear twice on the same invoice. Therefore, a primary key will be placed on inv_id and item_id. Here is how this definition will look:

create table inv_detail(
    inv_id int not null constraint FK_NC_detail_inv_id
        foreign key references invoices,
    item_id smallint not null constraint FK_NC_detail_item_id
        foreign key references item,
    qty smallint not null default 1,
    constraint PK_CL_detail_inv_item primary key
    (inv_id, item_id)
    )

A clustered index is placed on this primary key because when invoice data is requested, it will likely be a request for all items on a particular invoice. This will help to support that query by placing the data in invoice number order and then by item number order.

Inserting Data

The big first step is complete. If you have been entering these tables, too, you can now try entering some data. Your original task list indicated that there was some data to enter. Suppose you were given a stack of papers. On top are some invoices, so try entering those first.

Figure 9.1 shows the first invoice.

Figure 9.1.  A sales invoice for Wonderful Widgets, Inc.

To add this invoice to the invoices table, you need to include the cust_id. To get that, you need to add the customer to the customer table.

 insert customer(fname, lname, addr, city, state, phone)
         values(“John”, “Williams”, “102 East Highland Avenue”,
         “Imaginaryville”, “NY”, “6175558012”)
 select @@identity

(1 row(s) affected)

------------------------------------------
1
(1 row(s) affected)

The INSERT is pretty straightforward—it adds a customer to the table. Recall that adding a primary key requires only that the key be unique. The server knows that the PK is built on an identity column, and it has a unique index on that column, so you’re covered.

Selecting @@identity, a global variable, returns the last identity value that was inserted. This will return the number 1, because this is the first row you have added to the customer table.

Now that you have the cust_id, you can insert a row into invoices:

insert   invoices(cust_id,    saledate,    amt)

 values (1, “1/7/97”, $83.38)
 select @@identity

(1 row(s) affected)

------------------------------------------
1
(1 row(s) affected)

The next step will be to add rows to the inv_detail table. In order to reference item_id’s, though, you will need to add those items to the item table. Before doing that, I’d like to show you what happens when a key constraint is violated.

If you tried to insert a row into inv_detail and provided a bogus item_id number, here is what would happen:

 insert inv_detail(inv_id, item_id, qty)
 values(1, 1, 5)

 Msg 547, Level 16, State 2
 INSERT statement conflicted with COLUMN FOREIGN KEY constraint
      ‘FK_NC_detail_inv_id’. The conflict occurred in database
      ‘widgits’, table ‘invoices’, column ‘inv_id’
 Command has been aborted.

The reference constraints prevent you from introducing a foreign key that does not correspond to an existing primary key.

Okay—it’s time to add some items. There are three widget types here. Going over the other papers in your stack, you come across a purchase order, shown in Figure 9.2, listing the widgets you purchased for your inventory.

Figure 9.2.  A Wonderful Widgets, Inc. purchase order.

Quite a markup on the brainfood widgets! Here is the code to add these to the item table:

insert item(descr, price)
       values(“Blue All-Purpose Widgits”, $2)
insert item(descr, price, qty)
       values(“Mauve Economy Widgits”, $0.89, 500)
insert item(descr, price)
       values(“Deluxe Brainfood Widgits”, $3)
select * from item

(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
item_id descr                            price      qty
------- -------------------------------- ---------- ------
1       Blue All-Purpose Widgets         2.00       100
2       Mauve Economy Widgets            0.89       500
3       Deluxe Brainfood Widgets         3.00       100
(3 row(s) affected)

Now that the items are entered, you can do the inv_detail inserts. This table requires values for inv_id, item_id, and qty.

insert inv_detail(inv_id, item_id, qty)
       values(1, 1, 5)
insert inv_detail(inv_id, item_id, qty)
       values(1, 2, 1)
insert inv_detail(inv_id, item_id, qty)
       values(1, 3, 4)
select * from inv_detail

inv_id          item_id    qty
-----------     -------    ------
1               1          5
1               2          1
1               3          4
(3 row(s) affected)

Exercise Complete

This concludes the DRI exercise. The quiz at the end of the chapter asks for a few queries using these tables. If you’d like to experiment further, see what happens when you try to delete a row from customer. If you delete a customer that has invoices, the server will prevent you from doing it. See what happens to identity values when a constraint aborts the INSERT.