Database Access with Visual Basic .NET (3rd Edition)

Data Definition Language commands are SQL statements that enable you to create, manipulate, and destroy elements of the database structure. Using DDL, you can create and destroy tables and alter the definition of tables.

Data Definition Language commands are perhaps the least used statements in SQL, mainly because there are so many good tools that help you perform chores, such as creating tables, fields, and indexes. Visual Studio.NET issues SQL DDL commands behind the scenes when you design a database structure in Server Explorer, but it doesn't provide a facility to issue SQL DDL commands directly to the database. To do that, you must use SQL Query Analyzer and the osql command-line tool, or issue the DDL command in code.

If you're coming from a client-server programming environment, you might be more comfortable with using DDL to create the structure of your database. Like data manipulation commands, DDL commands don't return result sets (which is why they're referred to as "commands" rather than "queries").

Creating Database Elements with CREATE

New database elements can be created by using the SQL CREATE clause. To create a table, use the CREATE TABLE command, followed by the fields and data types you want to include in the table, delimited by commas and enclosed in parentheses. For example, to create a new table, you can use this SQL statement:

CREATE TABLE tblRegion (State char (2) , Region varchar (50))

The data type char(2) tells the database engine to create a fixed text field that can store a maximum of two characters; varchar(50) creates a variable-length field 50 characters long.

This query creates a table with the following parts:

tblRegion State Region

For a complete list of data types you can use when creating fields, see the Data Types section in Chapter 1.

Adding Constraints to Tables

You can add constraints when creating a table. A constraint is similar to an index, but it's used to designate a unique key, a primary key, or a foreign key.

You create a constraint by using the SQL CONSTRAINT clause. It takes two parameters: the name of the index and the name of the field or fields you're interested in indexing. You can declare the index to be UNIQUE or PRIMARY, in which case the index designates that the field can only accept unique values or that a field or fields serves the table's primary key.

Note

The concept of indexes having names might seem a little strange if you're accustomed to Microsoft Access; the reason is that Access buries the names of indexes in its user interface. You can get access to the name of an index programmatically, however.

For example, as an enhancement to the tblRegion table created in the preceding demonstration, you might add a unique index to the State field because it is used in joins. The query

CREATE TABLE tblRegion (State char (2), Region varchar (50), CONSTRAINT StateIndex UNIQUE (State))

creates the table with a unique index called StateIndex on the State field.

Although this code fragment indexes the State field, it might make more sense to make the State field the table's primary key. Doing so will index the field, ensure that no values are duplicated in the State field, and ensure that no null values appear in the State field. The following SQL creates the tblRegion table with the State field as its primary key:

CREATE TABLE tblRegion (State char (2), Region varchar (50), CONSTRAINT StatePrimary PRIMARY KEY (State))

Designating Foreign Keys

To designate a field as a foreign key, you can use the FOREIGN KEY constraint. For example, suppose that in your database design there is a one-to-many relationship between the State field in tblRegion and a corresponding State field in tblCustomer. The code, then, that you'd use to create tblCustomer might look like this:

CREATE TABLE tblCustomer (ID int identity(1, 1), [FirstName] varchar (20), [LastName] varchar (30), [Address] varchar (100), [City] varchar (75), [State] varchar (2), CONSTRAINT IDPrimary PRIMARY KEY ([ID]), CONSTRAINT StateForeign FOREIGN KEY ([State]) REFERENCES tblRegion ([State]))

Note that designating a foreign key in a CREATE TABLE command doesn't create an index on that foreign key; it serves only to create a relationship between the two tables.

Creating Indexes with CREATE INDEX

In addition to creating indexes when you create your table (using the CONSTRAINT clause), you can also create indexes after you've created the table (using the CREATE INDEX clause). This approach is useful when you want to create an index on a table that already exists (as opposed to the CONSTRAINT clause, which lets you create indexes only on tables when you create the table).

To create an index on an existing table, use

CREATE INDEX StateIndex ON tblCustomer ([State])

To create a unique index, use the UNIQUE keyword, as in

CREATE UNIQUE INDEX StateIndex ON tblRegion ([State])

To create a primary key on an existing table, use

CREATE UNIQUE NONCLUSTERED INDEX StateIndex ON dbo.tblRegion ( State ) ON [PRIMARY]

Deleting Tables and Indexes with DROP

You can delete database elements by using the DROP clause. For example, to delete tblRegion, use

DROP TABLE tblRegion

You can also drop an index in a table by using the DROP clause:

USE Novelty IF EXISTS (SELECT name FROM sysindexes WHERE name = 'StateIndex') DROP INDEX tblRegion.StateIndex GO

Note that, to delete a primary key, you must know the primary key's name.

To drop individual fields from tables, use a DROP clause within an ALTER TABLE clause, as discussed in the next section. Finally, to delete an entire database, use the DROP DATABASE clause.

Modifying a Table's Definition with ALTER

You can modify the definition of a field in a table by using the ALTER clause. For example, to add a CustomerType field to tblCustomer, use

ALTER TABLE tblCustomer ADD CustomerType int

To remove a field from a database, use the DROP COLUMN clause along with an ALTER TABLE clause:

ALTER TABLE tblCustomer DROP COLUMN CustomerType

You can also add constraints to a table by using the ALTER TABLE clause. For example, to create a relationship between tblCustomer and tblOrder with ALTER TABLE, use

ALTER TABLE tblOrder ADD CONSTRAINT OrderForeignKey FOREIGN KEY ([CustomerID]) REFERENCES tblCustomer ([ID])

Again, remember that adding a constraint doesn't create a conventional index on a field; it just makes a field unique, designates a field as a primary key, or creates a relationship between two tables.