SQL- Data Definition Language

Defining Database Tables

To create a table, we must specify Table name, Column names their corresponding Column data types with the Column sizes and Constraints if any.

Create Table

For naming conventions of Database Objects check the database documentation. For example below are some conventions/series of rules Oracle Corporation established for naming all database objects:

• From 1 to 30 characters
• Only alphanumeric characters, and special characters ($ , _, #)
  
• Must begin with a letter and can not contain blank spaces or hyphens
  

Syntax to create a table:

CREATE TABLE tablename (
    columnname1 data_type (size) [constraints],
    columnname2 data_type (size) [constraints],
    …
)

Let us start with a simple table definition; Let us define a table to hold employee information. Syntax with an example:

CREATE TABLE emp (
    empno INTEGER,
    ename VARCHAR(20),
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2)
);

Default Clause

Default: specifies a default value that is inserted automatically if the column value is not passed during INSERT statement.

Syntax:

CREATE TABLE tablename ( columnname datatype DEFAULT value );

Syntax with an example:

CREATE TABLE emp (
    empno INTEGER,
    ename VARCHAR(20),
    job VARCHAR(10) DEFAULT 'unknown',
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER DEFAULT 40,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2)
);

Database Table Constraints

A rule that restricts the values that can be inserted into a column. A mechanism used to protect the relationship between data within a table, or the correspondence between data in two different tables.

Types of Constraints

• Integrity constraints: define primary and foreign keys
• Value constraints: define specific data values or data ranges that must be inserted into columns and whether values must be unique or not NULL
  
• Table constraints: restricts the data value with respect to all other values in the table
  
• Column constraints: limits the value that can be placed in a specific column, irrespective of values that exist in other table records
  

Integrity Constraints

• Define primary key columns
• Specify foreign keys and their corresponding table and column references
  
• Specify composite keys
  

Primary Key Constraints

• Column-Level: the constraint will be given along with the column definition. This is used when only one column is to be made as a primary key
• Table-level: the constraint will be given at the end of the table definition. This is used to define a compound/composite primary key
  

Syntax with an example:

Column Level Primary Key Constraint:

CREATE TABLE emp (
    empno INTEGER CONSTRAINT pk_empno PRIMARY KEY,
    ename VARCHAR(20),
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2)
);

Table Level Primary Key Constraint:

CREATE TABLE emp (
    empno INTEGER,
    ename VARCHAR(20),
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2),
    CONSTRAINT pk_emp PRIMARY KEY( empno, ename )
);

Foreign Key Constraints

Can only be defined after column is defined as a primary key in the parent table

Syntax with an example:

Suppose we have a parent table that contains employee department info as follows:

CREATE TABLE dept (
    deptno INTEGER PRIMARY KEY,
    dname VARCHAR(20),
    loc VARCHAR(10)
);

Column Level Foreign Key Constraints:

CREATE TABLE emp (
    empno INTEGER PRIMARY KEY,
    ename VARCHAR(20),
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER CONSTRAINT fk_deptno REFERENCES dept( deptno ),
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2)
);

Table Level Foreign Key Constraints:

CREATE TABLE emp (
    empno INTEGER PRIMARY KEY,
    ename VARCHAR(20),
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2),
    CONSTRAINT fk_deptno FOREIGN KEY( deptno ) REFERENCES dept( deptno )
);

Types of Value Constraints

Check Constraint: restricts data to specific values

Syntax:

CREATE TABLE table name ( columnname datatype CHECK ( condition ) );

Syntax with an example

Column Level:

CREATE TABLE emp (
    empno INTEGER,
    ename VARCHAR(20),
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2) CONSTRAINT ck_sal CHECK ( sal > 0 ),
    comm DECIMAL(7, 2)
);

Table Level:

CREATE TABLE emp (
    empno INTEGER,
    ename VARCHAR(20),
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2),
    CONSTRAINT ck_sal_comm CHECK ( sal > 0 and comm >= 0 )
);

NOT NULL: specifies that a column cannot be empty

Syntax:

CREATE TABLE tablename ( columnname datatype NOT NULL );

Syntax with an example:

CREATE TABLE emp (
    empno INTEGER CONSTRAINT nn_empno NOT NULL,
    ename VARCHAR(20) NOT NULL,
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2)
);

UNIQUE: Specifies that a non-primary key column must have a unique value

Syntax:

CREATE TABLE table name ( columnname datatype UNIQUE );

Syntax with an example:

Column Level:

CREATE TABLE emp (
    empno INTEGER CONSTRAINT uq_empno UNIQUE,
    ename VARCHAR(20) UNIQUE,
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2)
);

Table Level:

CREATE TABLE emp (
    empno INTEGER,
    ename VARCHAR(20),
    job VARCHAR(10),
    mgr INTEGER,
    hiredate DATE,
    deptno INTEGER,
    sal DECIMAL(7, 2),
    comm DECIMAL(7, 2),
    CONSTRAINT uq_empno_name UNIQUE( empno, ename )
);

Modifying and Deleting Database Tables

• Delete an existing table from database
• Modify existing table by Changing the name of a table
  
• Adding new columns to existing table
  
• Deleting columns that are no longer needed
  
• Changing the data type or maximum size of an existing column
  
• Unrestricted action: some specifications can always be modified
  
• Restricted action: specifications modified only in certain situations
  

Deleting and Renaming Tables

To delete database table permanently from database. Deletes an object in the database, usually irretrievably, i.e., it cannot be rolled back. *Use with caution.

Syntax:

DROP TABLE tablename;

For Oracle, to delete foreign key constraints, add CASCADE CONSTRAINTS.

DROP TABLE tablename [CASCADE CONSTRAINTS];

Syntax with an example:

DROP TABLE emp;

To change the name of a table use RENAME

Syntax in Oracle:

Rename old_tablename to new_tablename;

Syntax with an example:

RENAME emp to employee;

Syntax in MS SQL:

exec sp_rename 'tablename', 'newtablename';

Here sp_rename is a Stored Procedure to rename objects

exec command is used to execute the procedure with proper parameters provided

Syntax with an example:

exec sp_rename 'emp', 'employee';

Adding Columns to Existing Tables

Adding a column to an existing table or a constraint.

Syntax:

ALTER TABLE tablename ADD columnname data_declaration [constraint];

Syntax with an example:

ALTER TABLE emp ADD load_date DATE;

Modifying existing Column data type

Modifies the structure of an existing object in database. Can change data type to compatible data types (i.e. varchar to char, integer to varchar) without deleting the existing data in table.

Syntax in Oracle:

ALTER tablename MODIFY( columnname new_datatype );

Syntax with an example:

ALTER TABLE emp MODIFY ( job VARCHAR(20) );

Syntax in MS SQL:

ALTER TABLE tablename ALTER COLUMN columnname new_datatype;

Syntax with an example:

ALTER TABLE emp ALTER COLUMN job VARCHAR(20);

Renaming existing Column

Syntax in Oracle:

ALTER TABLE tablename RENAME COLUMN columnname TO newcolumnname;

Syntax with an example:

ALTER TABLE emp RENAME COLUMN loaddate TO load_date;

Syntax in MS SQL:

exec sp_rename 'tablename.columnname', 'newcolumnname', 'COLUMN';

Here sp_rename is a Stored Procedure to rename objects. exec command is used to execute the procedure with proper parameters provided.

Syntax with an example:

exec sp_rename 'emp.loaddate', 'load_date', 'COLUMN';

Deleting a Column

This is used to remove a column from a table

Syntax:

ALTER TABLE tablename DROP COLUMN columnname;

Syntax with an example:

ALTER TABLE emp DROP COLUMN load_date;

Adding and Deleting Constraints

Syntax to Add a Constraint:

ALTER TABLE tablename ADD CONSTRAINT constraint_name constraint_definition;

Syntax to Remove a Constraint:

ALTER TABLE tablename DROP CONSTRAINT constraint_name;

Syntax with examples:

ALTER TABLE emp ADD CONSTRAINT cc_sal CHECK ( sal > 0 );

ALTER TABLE emp DROP CONSTRAINT cc_sal;

Enabling and Disabling Constraints

When modifying a database it can be useful to disable constraints. Constraints are enabled by default.

Syntax to disable a constraint in Oracle:

ALTER TABLE tablename DISABLE CONSTRAINT constraint_name;

Syntax to enable a constraint in Oracle:

ALTER TABLE tablename ENABLE CONSTRAINT constraint_name;

Syntax with examples:

ALTER TABLE emp DISABLE CONSTRAINT cc_sal;

ALTER TABLE emp ENABLE CONSTRAINT cc_sal;

Syntax to disable a constraint in MS SQL:

ALTER TABLE tablename NOCHECK CONSTRAINT constraint_name;

ALTER TABLE tablename CHECK CONSTRAINT constraint_name;

This option can only be used with FOREIGN KEY and CHECK constraints. DEFAULT, PRIMARY KEY, and UNIQUE constraints cannot be disabled.

Syntax with an example:

ALTER TABLE emp NOCHECK CONSTRAINT cc_sal;

ALTER TABLE emp CHECK CONSTRAINT cc_sal;

Deleting data from a table

TRUNCATE deletes all data in a table in a very fast way, as logs are not written unlike DELETE. Use with caution as the operation cannot be rolled back.

Syntax:

TRUNCATE TABLE tablename;

Syntax with an example:

TRUNCATE TABLE emp;

Create a Table from Another Table

We can create a table with the same table definition of an existing database table with or without data.

Syntax in Oracle:

CREATE TABLE tablename AS ( SELECT * FROM existingtable );

Syntax with an example:

--WITH DATA:
CREATE TABLE employee AS (
SELECT * FROM emp WHERE deptno = 10 );

--WITHOUT DATA:
CREATE TABLE employee AS (
SELECT * FROM emp WHERE 1 = 2 );

Syntax in MS SQL:

SELECT * INTO tablename FROM existingtable;

Syntax with an example:

--WITH DATA:
SELECT * INTO employee FROM emp WHERE deptno = 10;

--WITHOUT DATA:
SELECT * INTO employee FROM emp WHERE 1 = 2;