Adding Sample Data

Now, the good news (which is probably old news for you) is that the commands to insert new rows are identical for the databases covered in this book. In the following sections, you'll add some test values to the tables you created.

There's one catch, though: You don't need to (and in some cases you aren't allowed to) specify values for the ID columns because you want them to autonumber themselves. Although when adding new rows to the tables these values should receive consecutive values, starting with one, this isn't always guaranteed by the database (especially with Oracle because of the way sequences work).

In these examples, the ID columns always started with one and are incremented by one. However, if this isn't the same on your system, you'll need to manually specify the values on the foreign key columns. So be careful when adding rows to the Category and ProductCategory tables because these are the tables on which the FOREIGN KEY constraints are defined.

Tip

If you want to delete all the rows from a table and reset the auto-increment ID, you should truncate the table, as described in Chapter 12, "Working with Database Objects" (which works just like dropping and re-creating the table). Simply deleting the rows will not reset the auto-increment values.

If the values you try to insert aren't valid, the database will not accept them because of the referential integrity you established using the foreign keys.

Adding Departments

Let's now insert some values into the Department table:

INSERT INTO Department (Name, Description)
   VALUES ('Full Costumes', 'We have the best costumes on the internet!');
INSERT INTO Department (Name, Description)
   VALUES ('Costume Accessories', 'Accessories and fun items for you.');
INSERT INTO Department (Name, Description)
   VALUES ('Jokes and Tricks', 'Funny or frightening, but all harmless!');

Caution

Always remember to COMMIT the transaction after adding, updating, or deleting table rows if you're working in automatic-transactions mode. This is the default mode for Oracle and DB2. For more information, refer to Chapter 10, "Transactions."

Adding Categories

In this example, let's assume that the automatically created departments have the IDs 1, 2, and 3. If they have different IDs, you'll need to specify different values for the DepartmentID column when adding the categories:

INSERT INTO Category (DepartmentID, Name, Description)
   VALUES (1, 'Exotic Costumes', 'Sweet costumes for the party girl:');
INSERT INTO Category (DepartmentID, Name, Description)
   VALUES (1, 'Scary Costumes', 'Scary costumes for maximum chills:');
INSERT INTO Category (DepartmentID, Name, Description)
   VALUES (2, 'Masks', 'Items for the master of disguise:');
INSERT INTO Category (DepartmentID, Name, Description)
   VALUES (3, 'Sweet Stuff', 'Raise a smile wherever you go!');
INSERT INTO Category (DepartmentID, Name, Description)
   VALUES (3, 'Scary Stuff', 'Scary accessories for the practical joker:');

The new categories should have IDs from 1 to 5. Test to see that you can't reference a nonexistent DepartmentID. For example, the following query will fail if there isn't any department with the ID of 99:

INSERT INTO Category (DepartmentID, Name, Description)
   VALUES (99, 'Scary Stuff', 'Scary accessories for the practical joker:');

Adding Products

Here are the commands that add a few new products to your database:

INSERT INTO Product (Name, Description, Price, ImagePath,
                       OnCatalogPromotion, OnDepartmentPromotion)
VALUES ('Beast Mask',
        'Red-eyed and open-mouthed scary mask guaranteed to scare!',
        5.99, '20214.jpg', 1, 0);

INSERT INTO Product (Name, Description, Price, ImagePath,
                       OnCatalogPromotion, OnDepartmentPromotion)
VALUES ('Cleopatra Kit',
        'Full of Eastern promise. Includes headband, necklace and bracelet.',
        14.99, '20247.jpg', 0, 0);

INSERT INTO Product (Name, Description, Price, ImagePath,
                       OnCatalogPromotion, OnDepartmentPromotion)
VALUES ('Horned Devil Mask',
        'Full devil mask with horns. The perfect Halloween disguise!',
        5.99, '97023.jpg', 0, 1);

INSERT INTO Product (Name, Description, Price, ImagePath,
                       OnCatalogPromotion, OnDepartmentPromotion)
VALUES ('Miss Santa',
        'A stunning red-sequinned Santa dress. Includes dress, belt, cape,
        hat and boot covers. A perfect present.!',
        49.99, '20393.jpg', 0, 1);
INSERT INTO Product (Name, Description, Price, ImagePath,
                       OnCatalogPromotion, OnDepartmentPromotion)
VALUES ('Vampire Disguise',
        'Vampire Set consisting of wicked wig, fangs, and fake blood.',
        9.99, '325.jpg', 1, 0);

INSERT INTO Product (Name, Description, Price, ImagePath,
                       OnCatalogPromotion, OnDepartmentPromotion)
VALUES ('Sequinned Devil Horns',
        'Shiny red horns for the little devil inside you!',
        3.75, '20017.jpg', 0, 0);

INSERT INTO Product (Name, Description, Price, ImagePath,
                       OnCatalogPromotion, OnDepartmentPromotion)
VALUES ('Devil Horn Boppers',
        'These red glitter boppers are guaranteed to attract attention.
        They will soon be under your spell!',
        2.50, '21355.jpg', 1, 0);

INSERT INTO Product (Name, Description, Price, ImagePath,
                       OnCatalogPromotion, OnDepartmentPromotion)
VALUES ('Bug Eyed Glasses',
        'Bug-eyed glasses to astound and amuse.',
        2.75, '98413.jpg', 0, 1);

Connecting Products and Categories

Finally, you add values into the ProductCategory table, which establishes connections between the existing products and categories. We assume the categories and products inserted earlier received consecutive ID values:

INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (1,3);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,1);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (2,3);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (3,3);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (4,1);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (5,2);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,3);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (6,4);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (7,4);
INSERT INTO ProductCategory (ProductID, CategoryID) VALUES (8,5);