SQL Extensions   «Prev  Next»

Lesson 9

Oracle Table Constraints Conclusion

You have now completed a whirlwind tour of how to create tables and constraints.
In this module, you practiced creating a table with several columns and saw how to set the space parameters for a table and watched what happens when a table outgrows its initial allocation of space. You observed what happens when you drop a table. You also saw how to disable a constraint and when you need to do that.
In this module, you learned how to:
  1. Identify the components of the CREATE TABLE command
  2. Describe how tables, tablespaces, and datafiles fit together
  3. Identify the parameters that define storage space
  4. Describe the advantages of using PRIMARY KEY constraints
  5. Identify the syntax components of the FOREIGN KEY, CHECK, and UNIQUE constraints
  6. Change columns in an existing table
  7. Describe the effects of dropping a table and disabling or removing a constraint

How do I set the Space Parameters for a table in Oracle

In Oracle, you can use the CREATE TABLE statement to set the space parameters for a table. You can specify the storage clause with the following options:
  1. INITIAL: Specifies the initial amount of space allocated for the table.
  2. NEXT: Specifies the amount of space allocated for each extent.
  3. MINEXTENTS: Specifies the minimum number of extents allocated for the table.
  4. MAXEXTENTS: Specifies the maximum number of extents allocated for the table.
  5. PCTINCREASE: Specifies the percentage increase in space allocated for each extent.
  6. FREELISTS: Specifies the number of free lists for the table.
  7. FREELIST GROUPS: Specifies the number of free list groups for the table.

Here is an example of how to create a table with space parameters: 
CREATE TABLE mytable (
   id INT,
   name VARCHAR2(50),
   age INT
)
STORAGE (
   INITIAL 100K,
   NEXT 50K,
   MINEXTENTS 2,
   MAXEXTENTS 20,
   PCTINCREASE 0,
   FREELISTS 1,
   FREELIST GROUPS 1
);
Please keep in mind that some of the above options may be deprecated and you should check the Oracle documentation for the most recent information.

Glossary

In this module you were introduced to the following new glossary terms:
  1. Datafile: The physical files that Oracle uses to store data. A datafile is a contiguous segment of a disk drive.
  2. Block: Each tablespace is subdivided into increments called blocks. The exact size for a block depends on the operating system in which the database resides. One block is the smallest increment of space the database can allocate to a table.
  3. Primary key: One or more columns that define a unique row in a table. A table can contain only one primary key.
  4. Unique key: A second primary key in a table. A table can contain more than one unique key.
  5. Constraint: A constraint is a database rule that you define within the database to enforce certain conditions regarding the data that is stored in the database tables.
  6. Column constraint: Constraints that apply to one column.
  7. Table constraint: Constraints that apply to more than one column.
  8. Foreign key: A column or list of columns in one table that contain data that references the primary key of another table.
  9. Check constraint: A condition that is required for every row in the table.

In the next module you will use SQL to insert, update, and delete data within the Oracle database.

Removing TableConstraints - Quiz

Click the Quiz link below to answer a few questions about removing tables and constraints.

Removing TableConstraints - Quiz

Oracle Database SQL