Hour 9
Creating and Managing Database Objects
What You’ll Learn in This Hour:
▶ An introduction to database objects
▶ An introduction to schemas
▶ An introduction to tables
▶ The nature and attributes of tables
▶ Examples of creating and manipulating tables
▶ Table storage options
▶ Referential integrity and data consistency
In this hour, you learn about database objects: what they are, how they act, how they are stored, and how they relate to one another. Database objects are the logical units that compose the building blocks of the database. Most of the instruction during this hour revolves around tables, but keep in mind other database objects exist, many of which are discussed in later hours of study.
Database Objects and Schemas
A database object is any defined object in a database that is used to store or reference data. Some examples of database objects include tables, views, clusters, sequences, indexes, and synonyms. The table is this hour’s focus because it is the primary and simplest form of data storage in a relational database.
A schema is a collection of database objects normally associated with one particular database username. This username is called the schema owner, or the owner of the related group of objects. You might have one schema or multiple schemas in a database. The user is associated with only the schema of the same name, and often the terms are used interchangeably. Basically, any user who creates an object has just created it in his or her own schema unless that user specifically instructs it to be created in another one. Based on a user’s privileges within the database, then, the user has control over objects that are created, manipulated, and deleted. A schema can consist of a single table and has no limits to the number of objects that it may contain, unless restricted by a specific database implementation.
Say that the database administrator issues you a database username and password. Your username is USER1. Suppose that you log on to the database and create a table called EMPLOYEE_TBL. According to the database, your table’s actual name is USER1.EMPLOYEE_TBL. The schema name for that table is USER1, which is also the owner of that table. You have just created the first table of a schema.
The good part about schemas is that when you access a table that you own (in your own schema), you do not have to refer to the schema name. For instance, you can refer to your table as either one of the following:
EMPLOYEE_TBL USER1.EMPLOYEE_TBL
The first option is preferred because it requires fewer keystrokes. If another user queries one of your tables, the user must specify the schema as follows:
USER1.EMPLOYEE_TBL
As you progress through your SQL journey in this book, you learn how to grant users privileges to your schema so that they may access your objects. You also learn about synonyms, which enable you to give a table another name so that you do not have to specify the schema name when accessing a table. Figure 9.1 illustrates two schemas in a relational database.
FIGURE 9.1
Schemas in a database
In Figure 9.1, two user accounts in the database own tables: USER1 and USER2. Each user account has its own schema. Take a look at some examples of how the two users can access their own tables and the tables owned by the other user:
USER1 accesses own TABLE1: TABLE1 USER1 accesses own TEST: TEST USER1 accesses USER2's TABLE10 USER2.TABLE10 USER1 accesses USER2's TEST USER2.TEST
In this example, both users have a table called TEST. Tables can have the same names in a database, as long as they belong to different schemas. If you look at it this way, table names are always unique in a database because the schema owner is actually part of the table name. For instance, USER1.TEST is a different table than USER2.TEST. If you do not specify a schema with the table name when accessing tables in a database, the database server looks for a table that you own, by default. That is, if USER1 tries to access TEST, the database server looks for a USER1-owned table named TEST before it looks for other objects owned by USER1, such as synonyms to tables in another schema. Hour 24, “Working with the System Catalog,” helps you fully understand how synonyms work.
Be sure to understand the distinction between objects in your own schema and objects in another schema. If you do not provide a schema when performing operations that alter the table, such as a DROP command, the database assumes that you mean a table in your own schema. Always pay careful attention as to which user you are currently logged into the database with, to avoid unintentionally dropping the wrong object.
Caution
Object Naming Rules Differ Between Systems
Every database server has rules concerning how you can name objects and elements of objects, such as field names. Check your particular implementation for the exact naming conventions or rules.
Tables: The Primary Storage for Data
The table is the primary storage object for data in a relational database. In its simplest form, a table consists of row(s) and column(s), both of which hold the data. A table takes up physical space in a database and can be permanent or temporary.
Columns
A field, also called a column in a relational database, is part of a table that is assigned a specific data type. The data type determines what kind of data the column is allowed to hold. This enables the designer of the table to help maintain the integrity of the data.
Every database table must consist of at least one column. Columns are elements within a table that hold specific types of data, such as a person’s name or address. For example, a valid column in a customer table might be the customer’s name. The following data illustrates a column in a table:
BIRD_NAME WINGSPAN ------------------------------ ---------- Eastern Kingbird 15 Great Blue Heron 78 Mallard 3.2 Common Loon 54 Bald Eagle 84 Golden Eagle 90 Red Tailed Hawk 48 Osprey 72 Belted Kingfisher 23 Canadian Goose 72 Pied-billed Grebe 6.5 American Coot 29 Common Sea Gull 18 Ring-billed Gull 50 Double-crested Cormorant 54 Common Merganser 34 Turkey Vulture 72 American Crow 39.6 Green Heron 26.8 Mute Swan 94.8 Brown Pelican 90 Great Egret 67.2 Anhinga 42 Black Skimmer 15
Generally, a column name must be one continuous string and can be limited to the number of characters used according to each implementation of SQL. Underscores typically are used with names to provide separation between characters. For example, a column for the bird’s name can be named BIRD_NAME instead of BIRDNAME. This is normally done to increase the readability of database objects. You can use other naming conventions as well, such as CamelCase, to fit your specific preferences. As such, it is important for a database development team to agree upon a standard naming convention, to maintain order within the development process.
The most common form of data stored within a column is string data. This data can be stored as either upper case or lower case for character-defined fields. The case that you use for data is simply a matter of preference and should be based on how the data will be used. In many cases, data is stored in upper case for simplicity and consistency. However, if data is stored in different case types throughout the database (upper case, lower case, and mixed case), functions can be applied to convert the data to either upper case or lower case, if needed. Hour 15, “Restructuring the Appearance of Data,” covers these functions.
Columns also can be specified as NULL or NOT NULL. If a column is NOT NULL, something must be entered. If a column is specified as NULL, nothing has to be entered. NULL is different from an empty set, such as an empty string, and holds a special place in database design. As such, you can relate a NULL value to a lack of any data in the field.
Rows
A row is a record of data in a database table. For example, a row of data in a customer table might consist of a particular customer’s identification number, name, address, phone number, and fax number. A row consists of fields that contain data from one record in a table. A table can contain as little as one row of data and as many as several million rows of data or records. The following data from a table called FOOD provides an example of the distinct food items that birds eat from our database.
FOOD_ID FOOD_NAME
---------- ------------------------------
1 Seeds
2 Birds
3 Fruit
4 Frogs
5 Fish
6 Berries
7 Aquatic Plants
8 Aquatic Insects
9 Worms
10 Nuts
11 Rodents
12 Snakes
13 Small Mammals
14 Nectar
15 Pollen
16 Carrion
17 Moths
18 Ducks
19 Insects
20 Plants
21 Corn
22 Crayfish
23 Crustaceans
24 Reptiles
25 Deer
In the previous example, the table has 25 rows of data. The first row of data in the table appears as follows, consisting of the FOOD_ID and the FOOD_NAME:
1 Seeds
The CREATE TABLE Statement
The CREATE TABLE statement in SQL creates a table. Although the act of creating a table is quite simple, much time and effort should be put into planning the table structures before actually executing the CREATE TABLE statement. Carefully planning your table structure before implementation saves you from having to reconfigure in production.
Note
Types Used in This Hour
This hour’s examples use the popular data types CHAR (constant-length character), VARCHAR (variable-length character), NUMBER (numeric values, decimal, and nondecimal), and DATE (date and time values).
Some elementary questions need to be answered when creating a table:
▶ What type of data will be entered into the table?
▶ What will be the table’s name?
▶ What column(s) will comprise the primary key?
▶ What names will be given to the columns (fields)?
▶ What data type will be assigned to each column?
▶ What will be the allocated length for each column?
▶ Which columns in a table can be left as a null, or missing, value?
Note
Existing Systems Often Have Existing Naming Rules
Be sure to check your implementation for rules when naming objects and other database elements. Often database administrators adopt a naming convention that explains how to name the objects within the database so that you can easily discern how they are used.
After you answer these questions, using the actual CREATE TABLE statement is simple. The basic syntax for creating a table follows:
CREATE TABLE table_name ( field1 data_type [ not null ], field2 data_type [ not null ], field3 data_type [ not null ], field4 data_type [ not null ], field5 data_type [ not null ] );
Note that a semicolon is the last character in the previous statement. Also, brackets indicate portions that are optional. Most SQL implementations have some character that terminates a statement or submits a statement to the database server. Oracle, Microsoft SQL Server, and MySQL use the semicolon. Although Transact-SQL, Microsoft SQL Server’s ANSI SQL version, has no such requirement, it is considered best practice to use it. This book uses the semicolon.
Notice that the syntax to create a table looks very similar to the list of tables, columns, and data types at the end of the previous hour, as shown again:
BIRDS bird_id number(3) not null bird_name varchar(30) not null height number(4,2) not null weight number(4,2) not null wingspan number(4,2) null eggs number(2) not null broods number(1) null incubation number(2) not null fledging number(3) not null nest_builder char(1) not null
When you define a table using SQL, you simply provide the name of the table and a list of the columns in that table, along with their data types and whether the columns can contain null values. The default is null if it is not specified, which means that data is optional for that particular column.
To create a table called BIRDS, you simply substitute the table and column names that you previously defined during database design and substitute that information into the CREATE TABLE statement as follows:
create table birds (bird_id number(3) not null, bird_name varchar(30) not null, height number(4,2) not null, weight number(4,2) not null, wingspan number(4,2) null, eggs number(2) not null, broods number(1) null, incubation number(2) not null, fledging number(3) not null, nest_builder char(1) not null);
Note
Case Sensitivity of SQL Commands
Note that, with most implementations, the SQL command itself is not case sensitive. However, if you choose to store data a certain way, case sensitivity does matter when searching for that data. If you're not sure of the case, SQL has functions that enable you to convert the case of data, as covered beginning in Hour 15 of this book.
In this example, each record, or row of data, in the table consists of the following columns:
BIRD_ID, BIRD_NAME, HEIGHT, WEIGHT, WINGSPAN, EGGS, BROODS, INCUBATION, FLEDGIN, NEST_BUILDER
In this table, each field is a column. The column BIRD_ID can consist of one bird’s identification number or many birds’ identification numbers, depending on the requirements of a database query or transaction.
Eight different columns comprise this table. Notice the use of the underscore character to break up the column names into what appears to be separate words. (The bird identification is stored as the BIRD_ID.) This technique makes the table or column name more readable. Each column has been assigned a specific data type and length. By using the NULL/NOT NULL constraint, you specify which columns require values for every row of data in the table. The EMP_PHONE is defined as NULL, meaning that null values are allowed in this column because some individuals might not have a telephone number. A comma separates the information for each column, with parentheses surrounding all columns (a left parenthesis before the first column and a right parenthesis following the information on the last column).
Caution
Limitations on Data Types Vary
Check your particular implementation for the allowed name length limits and characters; they differ among implementations.
Naming Conventions
When selecting names for objects, specifically tables and columns, make sure the name reflects the data that is to be stored. For example, the name for a table pertaining to employee information might be named EMPLOYEE_TBL. Names for columns should follow the same logic. When storing an employee’s phone number, an obvious name for that column might be PHONE_NUMBER.
Using the ALTER TABLE Command
You can modify a table after the table has been created by using the ALTER TABLE command. You can add column(s), drop column(s), change column definitions, add and drop constraints, and, in some implementations, modify table STORAGE values. The standard syntax for the ALTER TABLE command follows:
alter table table_name [modify] [column column_name][datatype | null not null] [restrict|cascade] [drop] [constraint constraint_name] [add] [column] column definition
For example, let’s say that you want to remove the WINGSPAN column from the BIRDS table. Keep in mind that if you drop a column, the data is removed. On a related note, you cannot drop a column that has data that other child records in the database depend upon.
SQL> alter table birds 2 drop column wingspan; Table altered. SQL> desc birds Name Null? Type ----------------------------------------- -------- ---------------------------- BIRD_ID NOT NULL NUMBER(3) BIRD_NAME NOT NULL VARCHAR2(30) HEIGHT NOT NULL NUMBER(4,2) WEIGHT NOT NULL NUMBER(4,2) EGGS NOT NULL NUMBER(2) BROODS NUMBER(1) INCUBATION NOT NULL NUMBER(2) FLEDGING NOT NULL NUMBER(3) NEST_BUILDER NOT NULL CHAR(1)
In the following example, you attempt to remove the BIRD_ID from the BIRDS table. An error is received from the database because the BIRD_ID is defined as a primary key that has dependent child records in other tables. You cannot drop a column or remove data if that modification to the database violates any constraints or definitions that have previously been defined.
SQL> alter table birds
2 drop column bird_id;
drop column bird_id
*
ERROR at line 2:
ORA-12992: cannot drop parent key column
Note
Object Modifications Cannot Violate Existing Constraints
Remember that you cannot violate any rules that are previously set up for a database object when modifying a table or when inserting or modifying any data within a table.
In the following example, you add a column called BEAK_LENGTH to the BIRDS table.
SQL> alter table birds 2 add beak_length number(5,2); Table altered. SQL> desc birds Name Null? Type ---------------------------- -------- ----------------- BIRD_ID NOT NULL NUMBER(3) BIRD_NAME NOT NULL VARCHAR2(30) HEIGHT NOT NULL NUMBER(4,2) WEIGHT NOT NULL NUMBER(4,2) WINGSPAN NUMBER(4,2) EGGS NOT NULL NUMBER(2) BROODS NUMBER(1) INCUBATION NOT NULL NUMBER(2) FLEDGING NOT NULL NUMBER(3) NEST_BUILDER NOT NULL CHAR(1) BEAK_LENGTH NUMBER(5,2)
Caution
Altering or Dropping Tables Can Be Dangerous
Take heed when altering and dropping tables. If you make logical or typing mistakes when issuing these statements, you can lose important data.
Modifying Elements of a Table
The attributes of a column refer to the rules and behavior of data in a column. You can modify the attributes of a column by using the ALTER TABLE command. The word attributes here refers to the following:
▶ The data type of a column
▶ The length, precision, or scale of a column
▶ Whether the column can contain
NULLvalues
The following example uses the ALTER TABLE command on the BIRDS table to modify the attributes of the column BROODS:
SQL> alter table birds 2 modify broods not null; Table altered. SQL> desc birds Name Null? Type ------------------ -------- -------------------------- BIRD_ID NOT NULL NUMBER(3) BIRD_NAME NOT NULL VARCHAR2(30) HEIGHT NOT NULL NUMBER(4,2) WEIGHT NOT NULL NUMBER(4,2) WINGSPAN NUMBER(4,2) EGGS NOT NULL NUMBER(2) BROODS NOT NULL NUMBER(1) INCUBATION NOT NULL NUMBER(2) FLEDGING NOT NULL NUMBER(3) NEST_BUILDER NOT NULL CHAR(1) BEAK_LENGTH NUMBER(5,2)
Adding Mandatory Columns to a Table
One of the basic rules for adding columns to an existing table is that the column you are adding cannot be defined as NOT NULL if data currently exists in the table. NOT NULL means that a column must contain some value for every row of data in the table. If you are adding a column defined as NOT NULL, you are contradicting the NOT NULL constraint from the beginning if the preexisting rows of data in the table do not have values for the new column.
The following example attempts to modify the BEAK_LENGTH column in the birds table as Not Null, or as a mandatory column. You see in this example that an error is returned; you cannot identify an existing column as Not Null if rows of data already exist in that table and null values are found in that particular column.
SQL> alter table birds 2 modify beak_length not null; alter table birds * ERROR at line 1: ORA-02296: cannot enable (RYAN.) - null values found
However, you do have a way to add a mandatory column to a table:
Add the column and define it as
NULL. (The column does not have to contain a value.)Insert a value into the new column for every row of data in the table.
Alter the table to change the column’s attribute to
NOT NULL.
For example:
SQL> update birds 2 set beak_length = 0; 23 rows updated. SQL> commit; Commit complete. SQL> alter table birds 2 modify beak_length not null; Table altered.
Adding Autoincrementing Columns to a Table
Sometimes it is necessary to create a column that autoincrements itself, to give a unique sequence number for a particular row. You might do this for many reasons, such as if you do not have a natural key for the data or if you want to use a unique sequence number to sort the data. Creating an autoincrementing column is generally easy. In MySQL, the implementation provides the SERIAL method, to produce a truly unique value for the table. Following is an example:
CREATE TABLE TEST_INCREMENT(
ID SERIAL,
TEST_NAME VARCHAR(20));
Note
Using NULL for Table Creation
NULL is a default attribute for a column; therefore, it does not have to be entered in the CREATE TABLE statement. NOT NULL must always be specified.
In Microsoft SQL Server, you have an IDENTITY column type. The following is an example for the SQL Server implementation:
CREATE TABLE TEST_INCREMENT(
ID INT IDENTITY(1,1) NOT NULL,
TEST_NAME VARCHAR(20));
Oracle does not provide a direct method for an autoincrementing column. However, one method, using an object called a SEQUENCE and a TRIGGER, simulates the effect in Oracle.
Now you can insert values into the newly created table without specifying a value for the autoincrementing column:
INSERT INTO TEST_INCREMENT(TEST_NAME)
VALUES ('FRED'),('JOE'),('MIKE'),('TED');
SELECT * FROM TEST_INCREMENT;
| ID | TEST_NAME |
| 1 | FRED |
| 2 | JOE |
| 3 | MIKE |
| 4 | TED |
Modifying Columns
You need to consider many factors when modifying the existing columns of a table. Following are some common rules for modifying columns:
▶ The length of a column can be increased to the maximum length of the given data type.
▶ The length of a column can be decreased only if the largest value for that column in the table is less than or equal to the new length of the column.
▶ The number of digits for a number data type can always be increased.
▶ The number of digits for a number data type can be decreased only if the value with the most number of digits for that column is less than or equal to the new number of digits specified for the column.
▶ The number of decimal places for a number data type can be either increased or decreased.
▶ The data type of a column can normally be changed.
Some implementations restrict you from using certain ALTER TABLE options. For example, you might not be allowed to drop columns from a table. To do this, you have to drop the table itself and then rebuild the table with the desired columns. You might run into problems by dropping a column in one table that is dependent on a column in another table, or dropping a column that is referenced by a column in another table. Be sure to refer to your specific implementation documentation.
How to Create a Table from an Existing Table
You can create a copy of an existing table using a combination of the CREATE TABLE statement and the SELECT statement. The new table has the same column definitions. You can select any or all columns. New columns that you create using functions or a combination of columns automatically assume the size necessary to hold the data. The basic syntax for creating a table from another table follows:
create table new_table_name as select [ *|column1, column2 ] from table_name [ where ]
Take a look at some data in the BIRDS table. In this example, you’re looking at only the BIRD_NAME itself and the WINGSPAN. Notice that this table has 23 rows of data, in no particular order.
SQL> select bird_name, wingspan 2 from birds; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Mallard 3.2 Common Loon 54 Bald Eagle 84 Golden Eagle 90 Red Tailed Hawk 48 Osprey 72 Belted Kingfisher 23 Canadian Goose 72 Pied-billed Grebe 6.5 American Coot 29 Common Sea Gull 18 Ring-billed Gull 50 Double-crested Cormorant 54 Common Merganser 34 Turkey Vulture 72 American Crow 39.6 Green Heron 26.8 Mute Swan 94.8 Brown Pelican 90 Great Egret 67.2 Anhinga 42 Black Skimmer 15 23 rows selected.
You can create a copy of the BIRDS table with the CREATE TABLE AS statement, as follows. In this example, you’re creating a table called BIG_BIRDS that will contain only birds that have a wingspan of more than 48 inches.
SQL> create table big_birds as 2 select * from birds 3 where wingspan > 48; Table created.
Now if you query data from the BIG_BIRDS table, you get an idea of the capabilities of creating a table based on another table. In this example, the new table has only 12 rows; these are the big birds.
SQL> select bird_name, wingspan 2 from big_birds; BIRD_NAME WINGSPAN ------------------------------ ---------- Great Blue Heron 78 Common Loon 54 Bald Eagle 84 Golden Eagle 90 Osprey 72 Canadian Goose 72 Ring-billed Gull 50 Double-crested Cormorant 54 Turkey Vulture 72 Mute Swan 94.8 Brown Pelican 90 Great Egret 67.2 12 rows selected.
Notice the new keywords in the syntax, particularly the SELECT keyword. SELECT is a database query and is discussed in more detail in Hour 12, “Introduction to Database Queries.” However, it is important to know that you can create a table based on the results from a query.
Both MySQL and Oracle support the CREATE TABLE AS SELECT method of creating a table based on another table. Microsoft SQL Server uses a different statement. For that database implementation, you use a SELECT ... INTO statement, like this:
select [ *|column1, column2] into new_table_name from table_name [ where ]
You can take a look at some examples of using this method next. First, you do a simple query to view the data in the MIGRATION table:
select * from migration;
SQL> select * from migration;
MIGRATION_ID MIGRATION_LOCATION
------------ ------------------------------
1 Southern United States
2 Mexico
3 Central America
4 South America
5 No Significant Migration
6 Partial, Open Water
Next, you create a table called MIGRATION_NEW that is based on the previous query:
create table migration_new as select * from migration; Table created.
In SQL Server, the same statement is written like this:
select * into migration_new from migration; Table created.
Now if you run a query on the MIGRATION_NEW table, your results appear the same as if you selected data from the original table:
MIGRATION_ID MIGRATION_LOCATION
------------ ------------------------------
1 Southern United States
2 Mexico
3 Central America
4 South America
5 No Significant Migration
6 Partial, Open Water
Tip
What the * Means
SELECT * selects data from all fields in the given table. The * represents a complete row of data, or record, in the table. In other words, the query to the database is asking to show all columns for each row of data the query returns.
The Process of Dropping Tables
Dropping a table is one of the easiest actions to complete. When the RESTRICT option is used and the table is referenced by a view or a constraint, the DROP statement returns an error. When the CASCADE option is used, the drop succeeds and all referencing views and constraints are dropped. The syntax to drop a table follows:
drop table table_name [ restrict | cascade ]
SQL Server does not allow the use of the CASCADE option. For that particular implementation, you must ensure that you drop all objects that reference the table you are removing, to make sure you are not leaving an invalid object in your system.
In the first example that follows, you attempt to drop the BIRDS table:
SQL> drop table birds;
drop table birds
*
ERROR at line 1:
ORA-02449: unique/primary keys in table referenced by foreign keys
You can see that an error is returned because you cannot drop a table (or remove any data from the database, for that matter) that other data in the database depends on. BIRDS is one of the main tables in the database and is a parent table with primary keys that foreign keys in other tables reference.
In the next example, you drop the NICKNAMES table. The NICKNAMES table does have data, but it does not have any parent primary keys that other foreign keys in the database reference. Therefore, the table is dropped, along with all the data within it. The SELECT statement after the DROP TABLE statement shows that the table does not exist; an error is returned if you try to query a table that does not exist.
SQL> drop table nicknames;
Table dropped.
SQL> select * from nicknames;
select * from nicknames
*
ERROR at line 1:
ORA-00942: table or view does not exist
drop table products_tmp;
Table dropped.
Caution
Be Specific When Dropping a Table
Whenever you drop a table, be sure to specify the schema name or owner of the table before you submit your command, to avoid dropping the incorrect table. If you have access to multiple user accounts, make sure that you are connected to the database through the correct user account before you drop a table. For example, a safer way to drop the NICKNAMES table in the previous example is to fully qualify the table name with the table owner, as with DROP TABLE RYAN.NICKNAMES.
Integrity Constraints
Integrity constraints ensure the accuracy and consistency of data in a relational database. Data integrity is handled in a relational database through the concept of referential integrity. Many types of integrity constraints play a role in referential integrity (RI). Referential integrity consists of rules that are in place in the database to ensure that the data in tables remains consistent.
Primary Key Constraints
The primary key identifies one or more columns in a table that make a row of data unique. Although the primary key typically consists of one column in a table, more than one column can comprise the primary key. For example, either the employee’s Social Security number or an assigned employee identification number is the logical primary key for an employee table. The objective is for every record to have a unique primary key or value for the employee’s identification number. Because there is probably no need to have more than one record for each employee in an employee table, the employee identification number makes a logical primary key. The primary key is assigned upon table creation.
The following example identifies the BIRD_ID column as the PRIMARY KEY for the BIRDS table:
create table birds (bird_id number(3) not null primary key, bird_name varchar(30) not null, height number(4,2) not null, weight number(4,2) not null, wingspan number(4,2) null, eggs number(2) not null, broods number(1) null, incubation number(2) not null, fledging number(3) not null, nest_builder char(1) not null);
This method of defining a primary key is accomplished during table creation. In this case, the primary key is an implied constraint. You can also specify a primary key explicitly as a constraint when setting up a table, as follows:
create table birds (bird_id number(3) not null, bird_name varchar(30) not null, height number(4,2) not null, weight number(4,2) not null, wingspan number(4,2) null, eggs number(2) not null, broods number(1) null, incubation number(2) not null, fledging number(3) not null, nest_builder char(1) not null); PRIMARY KEY (BIRD_ID));
The primary key constraint in this example is defined after the column comma list in the CREATE TABLE statement.
You can define a primary key that consists of more than one column in either of the following methods, which demonstrate creating a primary key in an Oracle table:
create table nicknames (bird_id number(3) not null, nickname varchar(30) not null, constraint nicknames_pk primary key (bird_id, nickname));
In this example, let’s say that you did not specify the primary key when you created the NICKNAMES table. The following example shows how you can use the ALTER TABLE statement to add a constraint to an existing table.
ALTER TABLE NICKNAMES ADD CONSTRAINT NICKNAMES_PK PRIMARY KEY (BIRD_ID, NICKNAME) ;
Unique Constraints
A unique column constraint in a table is similar to a primary key: Each value in a column must be a unique value. Although a primary key constraint is placed on one column, you can place a unique constraint on another column even though it is not actually for use as the primary key.
Study the following example:
create table birds (bird_id number(3) not null primary key, bird_name varchar(30) not null unique, height number(4,2) not null, weight number(4,2) not null, wingspan number(4,2) null, eggs number(2) not null, broods number(1) null, incubation number(2) not null, fledging number(3) not null, nest_builder char(1) not null);
The primary key in this example is BIRD_ID, meaning that the bird identification number is the column that ensures that every record in the table is unique. The primary key is a column that is normally referenced in queries, particularly queries to join tables. The column BIRD_NAME has been designated as a UNIQUE value, meaning that no two birds can have the same name. Not a lot of difference exists between the two, except that the primary key provides an order to data in a table and, in the same respect, joins related tables.
Foreign Key Constraints
A foreign key is a column in a child table that references a primary key in the parent table. A foreign key constraint is the main mechanism that enforces referential integrity between tables in a relational database. A column defined as a foreign key references a column defined as a primary key in another table.
Study the creation of the foreign key in the following example:
create table nicknames (bird_id number(3) not null, nickname varchar(30) not null, constraint nicknames_pk primary key (bird_id, nickname), constraint nicknames_bird_id_fk foreign key (bird_id) references birds (bird_id));
The BIRD_ID column in this example has been designated as the foreign key for the NICKNAMES table. As you can see, this foreign key references the BIRD_ID column in the BIRDS table. This foreign key ensures that, for every BIRD_ID in the NICKNAMES table, there is a corresponding BIRD_ID in the BIRDS table. You might recall that this is called a parent/child relationship. The parent table is the BIRDS table, and the child table is the NICKNAMES table.
For every data value to be inserted for BIRD_ID in the child table of NICKNAMES, a value for BIRD_ID must first exist in the parent table of BIRDS. Likewise, for a value to be removed for BIRD_ID in the parent table, all corresponding first values for BIRD_ID must be removed from the child table. This is how referential integrity works—and how you use SQL to protect your data.
You can add a foreign key to a table using the ALTER TABLE command, as shown in the following example:
alter table nicknames add constraint nicknames_bird_id_fk foreign key (bird_id) references birds (bird_id);
Note
ALTER TABLE Variations
The options available with the ALTER TABLE command differ among implementations of SQL, particularly when dealing with constraints. In addition, the actual use and definitions of constraints vary. However, the concept of referential integrity works the same for all relational databases.
NOT NULL Constraints
Previous examples use the keywords NULL and NOT NULL listed on the same line as each column and after the data type. NOT NULL is a constraint that you can place on a table’s column. This constraint disallows the entrance of NULL values into a column; in other words, data is required in a NOT NULL column for each row of data in the table. NULL is generally the default for a column if NOT NULL is not specified, allowing NULL values in a column.
Using Check Constraints
You can use check (CHK) constraints to test the validity of data entered into particular table columns. Check constraints provide back-end database edits, although edits are commonly found in the front-end application as well. General edits restrict values that can be entered into columns or objects, whether within the database or on a front-end application. The check constraint is a way of providing another protective layer for the data.
The following example illustrates the use of a check constraint in Oracle:
create table birds (bird_id number(3) not null primary key, bird_name varchar(30) not null unique, height number(4,2) not null, weight number(4,2) not null, wingspan number(4,2) null, eggs number(2) not null, broods number(1) null, incubation number(2) not null, fledging number(3) not null, nest_builder char(1) not null, constraint chk_wingspan check (wingspan between 1 and 100));
In this example, the check constraint has been placed on the WINGSPAN column in the BIRDS table. For every row of data inserted into the BIRDS table, you place a rule or constraint here that the database checks to ensure that any value entered is between 1 and 100 inches. (We don’t imagine a bird having a wingspan greater than 100 inches. However, if that situation occurs in the future as the database grows, you can simply modify the table to accept a greater range of WINGSPAN.)
The Process of Dropping Constraints
Using the ALTER TABLE command with the DROP CONSTRAINT option, you can drop any constraint that you have defined. For example, to drop the primary key constraint in the EMPLOYEES table, you use the following command:
ALTER TABLE BIRDS DROP CONSTRAINT CHK_WINGSPAN; Table altered.
Some implementations provide shortcuts for dropping certain constraints. For example, to drop the primary key constraint for a table in MySQL, you use the following command:
ALTER TABLE BIRDS DROP PRIMARY KEY; Table altered.
Tip
Other Ways of Dealing with Constraints
Instead of permanently dropping a constraint from the database, some implementations allow you to temporarily disable constraints and then enable them later. The problem with this approach is that you cannot enable a constraint if data in a table violates the rules of the constraint.
Summary
During this hour, you learned a little about database objects in general, specifically about tables. The table is the simplest form of data storage in a relational database. Tables contain groups of logical information, such as employee, customer, or product information. A table consists of various columns, each with attributes; those attributes mainly consist of data types and constraints, such as NOT NULL values, primary keys, foreign keys, and unique values.
You learned about the CREATE TABLE command and options, such as storage parameters, that might be available with this command. You also learned how to modify the structure of existing tables using the ALTER TABLE command. Although the process of managing database tables might not be the most basic process in SQL, learning the structure and nature of tables helps you more easily grasp the concept of accessing the tables, whether through data manipulation operations or database queries. In later hours, you learn about managing other objects in SQL, such as indexes on tables and views.
Q&A
Q. When I name a table that I am creating, do I have to use a suffix such as _TBL?
A. Absolutely not. For example, a table that holds employee information can literally be named anything, but most likely similar to the following, so that the table is descriptive of the data it contains:
EMPLOYEES EMP_TBL EMPLOYEE_TBL EMPLOYEE_TABLE WORKER
Q. Why is using the schema name so important when dropping a table?
A. Consider this true story about a new DBA who dropped a table: A programmer had created a table under his schema with the same name as a production table. That particular programmer left the company. His database account was being deleted from the database, but the DROP USER statement returned an error because he owned outstanding objects. After some investigation, it was determined that his table was not needed, so a DROP TABLE statement was issued.
It worked like a charm, but the problem was that the DBA was logged in as the production schema when the DROP TABLE statement was issued. The DBA should have specified a schema name, or owner, for the table to be dropped. You guessed it: The wrong table in the wrong schema was dropped. Restoring the production database took approximately 8 hours.
Workshop
The following workshop consists of a series of quiz questions and practical exercises. The quiz questions are designed to test your overall understanding of the current material. The practical exercises give you the opportunity to apply the concepts discussed during the current hour, as well as build on the knowledge you acquired in previous hours of study. Be sure to complete the quiz questions and exercises before continuing to the next hour. Refer to Appendix C, “Answers to Quizzes and Exercises,” for answers.
Quiz
1. What is the most common object created in a database to store data?
2. Can you drop a column from a table?
3. What statement do you issue to create a primary key constraint on the preceding
BIRDStable?4. What statement do you issue on the preceding
BIRDStable to allow theWINGSPANcolumn to acceptNULLvalues?5. What statement do you use to restrict the birds added into the preceding
MIGRATIONtable to migrate only to certain migration locations?6. What statement do you use to add an autoincrementing column called
BIRD_IDto the precedingBIRDStable using both the MySQL and SQL Server syntax?7. What SQL statement can be used to create a copy of an existing table?
8. Can you drop a column from a table?
Exercises
In this exercise, refer to the examples in the previous hours for the BIRDS database, as well as the information provided for rescues and photographers that are integrated into the BIRDS database. At this point, you have been designing entities for photographer data that will be integrated into the BIRDS database. Review what you have come up with so far and use that information for the following exercises.
1. Using the SQL commands that you've learned during this hour, create a physical database based on the photographer data that you previously modeled that will be integrated with the
BIRDSdatabase.2. Think about any columns in your tables that can be altered in any way. Use the
ALTER TABLEcommand as an example to change the way one of your tables is defined.3. Have you used SQL and the
CREATE TABLEstatements to define all primary key and foreign key constraints? If not, use theALTER TABLEstatement to define at least one.4. Can you think of any check constraints that you can add to the photographer data? If so, use the
ALTER TABLEstatement to add the appropriate constraints.