Creating Tables - SQL - The Shortest Route For Beginners (2015)

SQL - The Shortest Route For Beginners (2015)

Chapter 7. Creating Tables

What is a Table?

A table is the basic object in a database which is created to store data. Each table in a database contains information for a particular category. For example, the Employees table, which you have been using so far, contains a list of employees along with other specific information about each employee. A database can have multiple tables to store information about each category separately. For instance, Departments is another table in the database which contains specific information about each department. Besides being separate, tables can also relate to each other using specific keys, known as primary and foreign keys. For example, the Employees table relates to the Departments table using the department id column. This column is created as a foreign key in the Employees table, which references a column with the same name in the Departments table, where it acts as a primary key. This kind of relationship helps in retrieving data from multiple tables (using Joins), as you saw in the previous chapter.

A table is the basic unit of data organization in a database. Each table has its own definition which comprises a table name and set of columns. A column identifies an attribute of the entity described by the table. For example, the column employee_id in the Employees table refers to the employee ID attribute of an employee entity. When you create a table, you specify columns that the table will carry and provide a name to each column, define data type for each column, and a width. You must specify a data type for each column in the table. Values subsequently inserted in a column assume the column data type. For example, the data type for employee_id is NUMBER(6), indicating that this column can only contain numeric data up to 6 digits in width. The width can be predetermined by the data type, as with DATE. After creating a table, you can insert, update, delete, and query rows using SQL. A row is a collection of column information corresponding to a record in a table. For example, a row in the employees table describes the attributes of a specific employee. The following figure illustrates the definition of the Employees table, along with rows in it.

This chapter is dedicated to handling tables, but first, you must acquaint yourself with data types which are declared for each column during the table creation process.

What are Data Types?

Each value that you store in database tables has a data type which associates a fixed set of properties with the value. These properties cause database to treat values of one data type differently from values of another. When a table is created, all of its column are specified with respective data types. These data types define the domain of values that each column can contain. For example, DATE columns cannot accept the value ‘RIAZ’ or the values 5 or 31 November. Similarly, a number column too doesn’t accept character values in it.

Just like many other differences found among different implementations, data types also vary from one DBMS to the next. Even the name of a data type in one DBMS can mean different things to others. Again, you are recommended to go through your DBMS documentations for details on its supported data types.

Another important thing that you must take care of when designing tables is the use of proper data types. You can suffer severe consequences in the future if you select wrong data types. Although you can change data types afterward, it is a real deadly task which can also cause loss of data.

The table that follows lists some of the most common data types that you will experiment with as a beginner.

DATA TYPE

DESCRIPTION

CHAR(size)

It stores fixed-length character data of length size characters. For example, the country id column, in the Country table residing in the database you are connected to, is defined as Char(2), which means that you cannot enter more than two characters in this column. In Oracle, the maximum size of this data type is 2000 characters. Default and minimum size is 1 character.

VARCHAR2(size)

This data type is used to store character strings of variable length. In some implementations it is defined as VARCHAR. In Oracle, you must specify a size for this data type, where its maximum size is 4000 characters, and minimum is 1 character.

NUMBER [(p [, s])]

As the name suggests, this one stores numbers having precision p and scale s. In Oracle, the precision p can range from 1 to 38, while the scale s can range from -84 to 127. Its default size is 38. There are some more numeric data types supported by other DBMSs e.g. Decimal, Int (small for Integer), Real, Money, Currency, and so on.

DATE

Used to store dates, this data type ranges from January 1, 4712 BC, to December 31, 9999 AD, and contains the datetime elements YEAR, MONTH, DAY, HOUR, MINUTE, and SECOND.

BLOB

A BLOB (Binary Large Object) is a data type that can hold large amount of data. BLOBs are handy for storing digitized information (e.g. images, audios, and videos).

NOTE: The CHAR and VARCHAR types are similar, but differ in the way they are stored and retrieved. CHAR data type accepts a fixed number of characters, whereas VARCHAR2 accepts text of any variable length (the maximum varies by DBMS). So, if the string 'Riaz Ahmed' is stored in a column specified as CHAR(25), a full 30 characters are stored, and the text is padded with spaces. In VARCHAR2 only the data specified is saved and no extra data is stored. If you assign a value to a CHAR or VARCHAR2 column that exceeds the column's maximum length, the value is truncated.

How to Create Tables?

Tables in a database are created by issuing CREATE TABLE command, which is one of the data definition language (DDL) command. To remind you, DDL commands are a subset of SQL commands used to create, modify, and remove database objects. This command has a very long syntax; however, here is the short and simple version to begin with.

Syntax :

Where:

Schema

A schema is a logical place in the database where you place new tables. It is an optional clause in this syntax. By default, the table is created in the schema you are currently connected to.

Table Name

It is a mandatory clause where you define a name for the new table. Follow the rules outlined below for naming database tables:

· Start table and column names with an alphabetic character (A-Z or a-z).

· The name can be 1-30 characters long.

· Names must contain only alphabets (A-Z or a-z), numbers (0-9), or three special characters( _[underscore], $ and #).

· Table name must not duplicate the name of another table in the same schema. Similarly, columns in the same table must not use the same name. However, columns in different tables can share the same name, and the same table name can be used in other schemas.

· Never use database reserve words (SELECT, USER etc.) to name tables.

· Use the same column entity in different tables for easy referencing. For example, the department number column is named DEPARTMENT_ID in Departments, Employees, and Job History tables.

· Table and column names are case-insensitive i.e. EMPLOYEES is treated as employees, which is the same as Employees.

Column

is the name of the column. Follow the instructions provided above for table naming.

Data Type

is the data type of the column with the desired length.

DEFAULT value

is an optional clause, which defines a default value for a column. The defined value gets added to the table when the corresponding column’s value is omitted at the time of record creation.

Constraint

Constraints are usually defined while creating a table and can be put either at column or table level. More details about constraints are provided in the next section.

What are Constraints?

We discussed referential integrity in the previous chapter. It is a mechanism in which keys are used to create references among database tables. To keep your database in a consistent state, you need a process which ensure that only valid data gets into the underlying tables. This process is implemented by means of integrity constraints – rules that restricts values in a database. Constraints are imposed on tables to enforce referential integrity to prevent loss of data consistency. The following table presents some major constraint types, supported by all major DBMSs. Note that the constraints are usually defined when tables are created. If needed, you can also enforce them at a later stage; however, the recommended way is to plan and implement them at an early stage, prior to inserting data in relevant tables.

The two major advantages of implementing constraints are:

· They enforce rules at the table level whenever a record is inserted, updated, or deleted. In order to execute these three operations successfully, the defined constraint(s) must be satisfied.

· Prevents deletion of table and records if there exist dependent tables and records. Such constraints are useful to eliminate chances of accidental deletions.

Most databases let you create the following five types of constraints and let you declare them syntactically in two ways.

· As part of the definition of an individual column. This is called inline specification.

· As part of the table definition. This is called out-of-line specification.

CONSTRAINT

DESCRIPTION

NOT NULL

Prohibits a table column value from being null. Columns without the NOT NULL constraint can contain null values by default. NOT NULL constraints must be declaredinline. All other constraints can be declared either inline or out of line.

The first example below creates a NOT NULL constraint for the phone number column, without specifying a constraint name.

CREATE TABLE Employees (…, phone_number varchar2(20) NOT NULL, …);

In the following example, the NOT NULL constraint is applied to the first name column. In this case, an expressive constraint name (emp_fname_nn) is provided, which stands foremployee first name not null. Providing meaningful names to constraints help in easy future referencing.

CREATE TABLE Employees

(…, first_name varchar2(20) CONSTRAINT emp_fname_nn NOT NULL, …);

UNIQUE

It prohibits multiple rows from having the same value in the same column, and allows null values if it is based on a single column. You can also create composite unique key which designates a combination of columns as the unique key. Use the UNIQUE keyword when you define this constraint inline. You must also specify one or more columns when it is defined as out of line constraint. You must define a composite unique key out of line. You cannot designate the same column or combination of columns as both a primary key and a unique key. The phone number column in the Employees table is a good candidate for this constraint, because every employee has a unique phone number. By making this column unique you can avoid duplicate values that might arise from typos. The following sample shows how to define an inline UNIQUE constraint.

CREATE TABLE Employees

(…, phone_number varchar2(20) CONSTRAINT emp_phone_uk UNIQUE, …);

The following example defines an out of line composite unique key on the combination of the employee_id and phone_number columns. The emp_id_phone_uk constraint ensures that the same combination of employee id and phone number values does not appear in the table twice.

CREATE TABLE Employees

(employee_id number(6), ..., phone_number varchar2(20), hire_date date, ...,

CONSTRAINT emp_id_phone_uk UNIQUE (employee_id, phone_number));

At first glance the UNIQUE constraint looks similar to the Primary Key constraint, but it is not the same as or synonymous to a PRIMARY KEY constraint. It differs from a primary key in the following ways:

· You can define multiple UNIQUE keys in a table, but a table can have one and only one PRIMARY KEY.

· Columns defined as UNIQUE constraints can have NULL values; PRIMARY KEY columns can't.

· A PRIMARY KEY can be defined as a Foreign Key in other tables; such a relationship cannot be created for UNIQUE keys.

· You can modify values in a UNIQUE key in a table as compared to values stored in a PRIMARY KEY which are not modifiable.

PRIMARY KEY

Uniquely identified each row in a table. You use it to combine the above two constraints in a single declaration. A primary key cannot be null and must not allow duplicates. This constraint is basically applied to ensure that other DML operations (such as UPDATE and DELETE) execute successfully. In the absence of a Primary Key, these two commands would never know which rows a user intend to manipulate. Primary Key constraints can be defined inline or out of line. A composite Primary Key must be defined out of line. See Chapter 1 for more details on primary keys.

A Primary Key constraint is usually defined inline when it is based on just one column, as shown here:

CREATE TABLE Employees

(employee_id number(6) CONSTRAINT emp_id_pk PRIMARY KEY,

...);

If it is based on multiple columns, then you must define this constraint out of line, like the one that follows. This constraint, which is called Composite Primary Key, ensures that an employee is not enrolled again in the same department.

CREATE TABLE Employees

(employee_id number(6), ..., department_id number(4),

CONSTRAINT emp_id_pk PRIMARY KEY (employee_id, department_id));

FOREIGN KEY

The FOREIGN KEY constraint designates a column or set of columns as a foreign key. It is created to establish and enforce relationships among tables. Also see Chapter 1 for more details on Foreign Keys.

Remember the following key points related to FOREIGN KEYS:

· The table containing the foreign key is called the child table, and the table containing the referenced key is called the parent table. A foreign key value must match an existing value in the parent table.

· Columns of the foreign key and the referenced key must match in order and data type.

· You can define a foreign key constraint on a single key column either inline or out of line; a composite foreign key must be specified on out of line attribute.

· You can define multiple foreign keys in a table. Also, a single column can be part of more than one foreign key.

· Since no part of a primary key can be null, therefore, a foreign key that is part of a primary key cannot be null as well.

The following clauses are used when you define foreign key constraints:

FOREIGN KEY This keyword is used only when you define the constraint out of line.

REFERENCES It is used with both inline and out of line declaration to identify the parent table and its column(s).

ON DELETE CASCADE Besides enforcing referential integrity, foreign keys are defined to prevent accidental deletion of records from the parent table(s). For example, you cannot delete a department which has employees. Such a department's record can be deleted either by deleting records of all the employees enrolled under it, or by using the ON DELETE CASCADE option. It is used when you want to remove a parent record along with all child records at once. Without this option, the row in the parent table cannot be deleted if it is referenced in the child table.

The following statement defines a foreign key on the department_id column that references the primary key on the department_id column in the Departments table:

CREATE TABLE Employee

(employee_id number(4), ...,

department_id CONSTRAINT emp_deptno_fk REFERENCES Departments(department_id) );

The constraint emp_deptno_fk ensures that a department given to an employee in the Employees table is present in the Departments table. However, employees can have null department numbers, meaning that they do not relate to any department. If provision of department is mandatory, then you could create a NOT NULL constraint on the department_id column in the Employees table in addition to the REFERENCES constraint. Also note that the above example didn't use the FOREIGN KEY clause, because the constraint was defined inline. Moreover, in Oracle, the data type of the department_id column is not needed as well, because it is done automatically by Oracle using the data type of the referenced key.

The following statement defines this foreign key constraint out of line:

CREATE TABLE Employee

(employee_id number(4), ...,

department_id, CONSTRAINT emp_deptno_fk FOREIGN KEY (department_id) REFERENCES Departments(department_id) );

Both these variations of the foreign key constraint omitted the ON DELETE CASCADE clause, instructing DBMS to not delete a department if an employee is associated with it.

CHECK

It is applied on table columns to ensure that the value being stored complies with the specified condition. The CHECK constraint uses the same syntax for both inline and out-of-line attributes. However, inline specification can refer only to the column currently being defined, whereas out-of-line specification can refer to multiple columns.

The following statement creates a table and defines a check constraint in each column of the table:

CREATE TABLE departments

(department_id NUMBER CONSTRAINT dept_no_chk

CHECK (department_id BETWEEN 10 AND 99),

department_name VARCHAR2(30) CONSTRAINT dept_name_chk

CHECK (department_name = UPPER(department_name)),

location VARCHAR2(10) CONSTRAINT dept_loc_chk

CHECK (location IN ('SEATTLE','TORONTO','TOKYO','LONDON')));

The purpose of all the three constraints defined in the above table is to restrict the values in respective columns.

dept_no_chk ensures that no department numbers are less than 10 or greater than 99.

dept_name_chk ensures that all division names are in uppercase.

dept_loc_chk restricts locations to Seattle, Toronto, Tokyo, or London.

The following statement creates a table with an out of line check constraint:

CREATE TABLE employees

(…, salary NUMBER(8,2), commission_pct NUMBER(5,2), ...,

CONSTRAINT emp_sal_chk CHECK (salary * commission_pct <= 5000));

The emp_sal_chk constraint implements a condition which puts an upper cap of 5000 to limit total commission of each employee by comparing the product of salary and commission percent with the defined upper limit. If you enter a new record in this table with some values for both salary and commission, then the product of these values must not exceed 5000 in order to comply with the constraint. On the other hand, if any of these columns has a null value, then the product of the calculation is also null which automatically satisfies the constraint.

Create a Table

By now I assume that not only you have grasped the basic concepts about constraints, but have also become familiar with the use of CREATE TABLE command. To complete the exercise, let’s create a table and implement all the five constraints in it.

SQL Statement :

CREATE TABLE department_clone

(

department_id number(4) CONSTRAINT pk_dept_id PRIMARY KEY,

department_name varchar2(3) CONSTRAINT nn_dept_name NOT NULL,

location_id number(4) CONSTRAINT fk_dept_loc REFERENCES locations (location_id),

CONSTRAINT uk_dept_name_loc UNIQUE (department_name, location_id)

);

Explanation :

CONSTRAINT

EXPLANATION

PK_DEPT_ID

This is an inline constraint which identifies the department id column as theprimary key of the department_clone table. By defining this constraint, you eliminated the chances of having the same number for two different departments, and also declared that this column must not accept NULL values.

NN_DEPT_NAME

This is also an inline constraint, defined to implement the NOT NULL constraint. It ensures that each department in the table must have a name.

FK_DEPT_LOC

This inline constraint is created to implement foreign key constraint. It guarantees that any location id entered in this table must already exist in the Locations table. Note that before defining this foreign key constraint the Locations table must exist, with a primary key constraint on the id column, prior to executing this entire CREATE statement.

UK_DEPT_NAME_LOC

It’s a unique key constraint defined as out-of-line. It uses two columns from the table to form a composite unique key to ensure that the same combination of department name and location id doesn’t appear in the table again.

Create Table From Another Table

A situation has surfaced that demands you to create a new table based on an existing one. Not only must you create the new table with the same structure, but you must also incorporate all the records from the existing table into the new one. Does SQL provides a solution to cope with this scenario? In fact, yes, it does. It is an alternate method to creating a table in which you use a subquery with the AS clause of the CREATE TABLE command to both create the table and insert rows into it. Here is its syntax:

Syntax :

CREATE TABLE {table name}

[(column specification, column specification, …)]

{AS subquery};

Let’s go through some examples to understand the above syntax.

CREATE TABLE emp2 AS SELECT * FROM Employees;

The above statement creates an exact copy of the Employees table. It creates the new table (emp2) with the same structure and populates it with all records from the source table. Note that this example didn’t use the optional column specification clause. When no column specifications are provided, then the column names of the target table are the same as the column names in the source table. Also note that no integrity constraints associated with the source table are inherited to the target table.

CREATE TABLE emp3 (id, name, hiredate) AS SELECT employee_id,first_name,hire_date FROM Employees;

In this statement the new table is created using some specific columns from the source table. In this type of table creation you can set names for the column in the target table different from the source columns, but the number of new columns must equal the number of columns in the subquery SELECT list.

CREATE TABLE emp4 AS SELECT * FROM Employees WHERE department_id=50;

Here, you created a new table using all columns (*) from the source table, just like the first statement above. It differs from the previous one in the way that, it uses the WHERE clause to limit the record insertion in accordance with the specified condition, which instructs to only insert records of employees working in department number 50.

NOTE: Since the subquery is based on the SELECT statement, you can use other clauses of this command as well, including WHERE and GROUP BY. You can also use joins in the subquery to insert data from multiple tables. Irrespective of the number of the source tables defined in the FROM clause, data will only be fetched into a single table.

Alter Table

After creating a table you realized that something went wrong with it creation and you need to change its definition. After creating a table you can modify its structure by using ALTER TABLE command. It is a data definition command (DDL) which allows you to:

· Add a new column to the table

· Modify column width

· Delete a column from the table

· Rename a column

· Rename tables

· Add/drop constraints

· Enable/disable constraints

· Rename constraints

NOTE: Take special care while using DDL commands as these command are irreversible. Once executed, you cannot roll them back.

Add Column

Let’s start the proceedings by adding a new column to a table which you can do using the ADD clause of the ALTER TABLE command. The ALTER TABLE command’s syntax is very similar to CREATE TABLE with the exception of three clauses (ADD, MODIFY and DROP), as shown below. This exercise assumes that you already have emp2 table, which you created on the previous page.

Syntax :

ALTER TABLE {table name}

ADD ({column datatype [DEFAULT value] [constraint specification]}[, column … , …]

);

SQL Statement :

ALTER TABLE emp2

ADD (ss_number char(9) CONSTRAINT emp_phone_uk UNIQUE );

In the above statement, you added a new column to record social security numbers of employees. Since this number has a fixed format, we used the CHAR data type. Each employee has a unique social security number, therefore, we created a UNIQUE constraint for this column, to eliminate duplicate values. Also note that we intentionally provided a wrong constraint name (emp_phone_uk), which will be dropped in a subsequent section.

Modify Column

After adding the social security column to the Employees table, you realized that you made couple of mistakes in the ALTER TABLE statement as well. First, you should have set the size of this column to 11 instead of 9 to save the number in 999-99-9999 format – including the two dashes. Secondly, the name of the unique constraint was also wrong. In the following statement, you’ll resolve the first issue by resizing the column width using the MODIFY clause of the command. Always keep the following guidelines in mind when you modify column definitions:

· Decrease the width of a column only when it contains null values.

· Change the data type if there are no values in the column.

· The DEFAULT value takes effect for subsequent insertions.

· Use the NOT NULL constraint only when there are no values.

Syntax :

ALTER TABLE {table name}

MODIFY {(column datatype [DEFAULT value] }[, column … , …]

);

SQL Statement :

ALTER TABLE emp2 MODIFY (ss_number char(11) );

Output :

Delete Column

You are also allowed to drop columns from a table provided that they do not contain values. The syntax here shows the two flavors of this clause. Use the first one if you want to drop a single column. For this scenario, the COLUMN keyword must follow the DROP clause, followed by the column name. Apply the second one if you need to drop multiple columns. For this, use the DROP clause only, followed by a list of columns enclosed in parentheses, and separated by commas.

Syntax :

ALTER TABLE {table name} {DROP COLUMN column name};

OR

ALTER TABLE {table name} {DROP (col_name1, col_name2, …)};

SQL Statements :

ALTER TABLE emp2 DROP COLUMN ss_number ;

NOTE: Dropping a column also drops dependent constraints. For example, if you successfully execute the example statement presented here, the associated UNIQUE constraint (emp_phone_uk) will drop as well. You can verify this drop by clicking the Constraint tab, as illustrated below.

Rename Column

After creating a table you can change the name of its columns considering the following restrictions.

· The new name must not conflict with the name of any existing column in the table.

· Dependent views, triggers, functions, and procedures can become invalid, and you may need to alter these objects with the new column name.

The following example renames the salary column of the emp2 table to monthly_salary.

Syntax :

ALTER TABLE {table name}

RENAME COLUMN {old column TO new column};

SQL Statement :

ALTER TABLE emp2 RENAME COLUMN salary TO monthly_salary;

NOTE: In Microsoft SQL Server execute the sp_rename procedure to change the name of a table. The following example renames the test1 table to test2 in the HR schema.

EXEC sp_rename 'hr.test1', 'test2';

GO

Renaming Table

Use the RENAME TO clause of the ALTER TABLE command to change the name of an existing table. The following statement renames an existing table emp4 to emp5.

Syntax :

ALTER TABLE previous table name RENAME TO new table name;

SQL Statement :

ALTER TABLE emp4 RENAME TO emp5;

Add Constraint After Creating a Table

To add a constraint to an existing table you use the ADD CONSTRAINT clause of the ALTER TABLE command. The following statements show how to add all the four constraints to a table after its creation. The third statement adds the social security column again (because it was dropped in the previous section), to implement the UNIQUE constraint.

Syntax :

ALTER TABLE {table name}

{ADD CONSTRAINT [constraint name] {constraint type (column | expression)};

SQL Statements :

ALTER TABLE emp2 ADD CONSTRAINT pk_emp2_id PRIMARY KEY (employee_id) ;

ALTER TABLE emp2 ADD CONSTRAINT fk_emp2_dept_id FOREIGN KEY (department_id)

REFERENCES Departments(department_id);

ALTER TABLE emp2 ADD (ss_number char(11)); -- recreated the SS number column

ALTER TABLE emp2 ADD CONSTRAINT uk_emp2_ss_number UNIQUE (ss_number) ;

ALTER TABLE emp2 ADD CONSTRAINT ck_emp2_salary CHECK (salary > 0);

NOTE: SQLite only supports RENAME TABLE and ADD COLUMN clauses of the ALTER TABLE command. Other variants, such as DROP COLUMN, ADD and DROP CONSTRAINT are not supported by this DBMS. To correct a table, you will have to create a new table with proper definitions, transfer the data (using INSERT INTO command) from the old table, drop the old table, and rename the new table as the old one. This solution is applicable to simple databases; however, you’ll have to do much more to handle a complex database.

Drop Constraint

Use the DROP clause of the ALTER TABLE command to remove a constraint. Its syntax contains the following sub-clauses:

PRIMARY KEY Use the PRIMARY KEY keyword to drop the primary key constraint of a table.

UNIQUE Specify UNIQUE to drop the unique constraint on the specified columns.

CONSTRAINT If the constraint being dropped is neither a Primary Key nor a Unique key, then use the CONSTRAINT clause followed by the constraint name to drop an integrity constraint.

CASCADE The CASCADE option causes any dependent constraints also to be dropped. You cannot drop a primary key or unique key constraint that is part of a referential integrity constraint without also dropping the foreign key. To drop the referenced key and the foreign key together, use the CASCADE clause.

Syntax :

ALTER TABLE {table name}

DROP PRIMARY KEY | UNIQUE (column) | CONSTRAINT constraint name [CASCADE];

SQL Statements :

CREATE TABLE dept2 AS SELECT * FROM Departments;

CREATE TABLE emp4 AS SELECT * FROM Employees;

ALTER TABLE dept2 ADD CONSTRAINT pk_dept2_id PRIMARY KEY (department_id) ;

ALTER TABLE emp4 ADD CONSTRAINT pk_emp4_id PRIMARY KEY (employee_id) ;

ALTER TABLE emp4 ADD CONSTRAINT fk_emp4_dept_id FOREIGN KEY (department_id)

REFERENCES dept2(department_id);

ALTER TABLE dept2 DROP PRIMARY KEY CASCADE;

The above statement removes the primary key constraint on the dept2 table and drops the associated foreign key constraint (fk_emp4_dept_id) defined on the department_id column in the emp4 table.

ALTER TABLE emp2 DROP UNIQUE(ss_number);

To drop a unique constraint use the UNIQUE keyword followed by the column name. The constraint was added to the table on the previous page.

ALTER TABLE emp2 DROP CONSTRAINT fk_emp2_dept_id;

This one drops the foreign key constraint which was created on the previous page. Use the CONSTRAINT clause to drop an integrity constraint other than a primary key or unique constraint.

Enable/Disable Constraint

In situations where you want to block an existing constraint temporarily without permanently dropping it, you can use the ENABLE/DISABLE clauses of the ALTER TABLE command. The DISABLE clause deactivates an integrity constraint, and applying the CASCADE option with it disables dependent constraints as well. Once you’re out of the situation, simply turn the constraint back on using the ENABLE clause. These two clauses can be used in both the CREATE and ALTER table commands.

Syntax :

ALTER TABLE {table name}

DISABLE | ENABLE CONSTRAINT constraint name [CASCADE];

SQL Statements :

ALTER TABLE emp2 DISABLE CONSTRAINT ck_emp2_salary; -- constraint deactivated

ALTER TABLE emp2 ENABLE CONSTRAINT ck_emp2_salary; -- constraint activated

Renaming Constraint

The RENAME CONSTRAINT clause of the ALTER TABLE command lets you rename any existing constraint. Note that you cannot use the name of an existing constraint in the same schema. Renaming a constraint has no impact on the dependent objects which remain valid. The following statement renames the old constraint name (ck_emp2_salary) on the emp2 table to ck_emp2_min_salary.

Syntax :

ALTER TABLE {table name}

RENAME CONSTRAINT old constraint name TO new constraint name;

SQL Statement :

ALTER TABLE emp2 RENAME CONSTRAINT ck_emp2_salary TO ck_emp2_min_salary;

Remove a Table

To remove a table from the database you use the DROP TABLE command. It is a DDL command which, when issued, permanently deletes the specified table along with all the data in the table. Keep the following points in mind before using this vital command:

· There is no Are you sure? confirmation from the database.

· This command is irreversible, which means that once this command executes successfully, you’ll permanently lose the table and its data immediately.

· Other dependent objects (views, stored procedures, functions, and so on) will become invalid.

· You cannot drop a table with active referential integrity constraints. For this, you have to use the CASCADE CONSTRAINTS option which also drops dependent integrity constraints.

· The good news is that only the owner of the table or a person provided with relevant privileges can perform this operation.

Syntax :

DROP TABLE {table name} [CASCADE CONSTRAINTS];

SQL Statement :

DROP TABLE emp5 CASCADE CONSTRAINTS;

Test Your Skill

1. A table definition consists of how many components?

2. Out of the above table definition components, how many of them are mandatory?

3. Identify a value which can be stored in a numeric column:

1. ‘Salary’ c. ’ 01-JAN-2015’

2. ’10,000’ d. 145.65

4. A character column can accept the following values. Select all that apply.

1. ‘Salary’ c. ’01-JAN-2015’

2. ’10,000’ d. 145.65

5. Which data type is used to store digitized data?

1. Number c. Date

2. BLOB d. VARCHAR

6. Which constraint is used to prohibit null values?

7. The purpose of implementing the UNIQUE constraint is to:

1. Add Primary Keys

2. Add Foreign Keys

3. Prevent duplicate values

4. Accept null values

8. What constraint would you define for the gender column, and how?

9. What sequence would you follow to create related parent and child tables?

1. Create child and then the parent table.

2. Create parent before the child table.

10.In which sequence would you drop related parent and child tables?

1. Drop parent before the child.

2. Drop child before the parent.

11.After creating a table you realized a mistake in the name of a column? How would you correct this mistake?

12.Are the following examples syntactically correct?

1. Create Table Test_1

(id number(6), name varchar2(30) constraint pk_test_1 primary key(id));

2. Create Table Orders

(

order_id number(6),

customer_id number(6), constraint nn_ord_cust_id NOT NULL,

order_total number(12,2),

dispatched char(1) constraint ck_ord_dispatch CHECK(dispatched IN ('Y','N')),

constraint pk_ord_id primary key

);