ou use a DROP TABLE command to delete a table.
Format:

          DROP TABLE table_name;

> If you DROP a table, it means that you lose all of the table’s structure and parameters.

> Some RDBMS systems will not allow you to DROP a table referenced by a FOREIGN KEY until the FOREIGN KEY constraint or the table containing the FOREIGN KEY constraint has been removed.

> Dropping a table does not remove a view, but a view can not function without a base table.

> A table can be empty (no data) without being dropped.

You use an ALTER TABLE comand to change names, data types, defaults, and constraints.Format:

  ALTER TABLE table_name
      {[ADD [COLUMN] column_name data_type [constraints]         Add a new column
     | [ALTER [COLUMN] column_name SET DEFAULT default_value]    Change a column default
     | [ALTER [COLUMN] column_name DROP DEFAULT]                 Remove a column default
     | [DROP [COLUMN] column_name [RESTRICT | CASCADE]]          Remove a column
     | [ADD CONSTRAINT table_constraint]                         Create a table constraint
     | [DROP CONSTRAINT constraint_name [RESTRICT | CASCADE]     Remove a table constraint
     | [RENAME TO new_table_name]};                              Rename a table

> You can create, change, or remove a table or column constraint, such as a primary key, foreign key, unique, or check constraint. ADD creates an entry, ALTER changes an entry, and DROP removes an entry.

> The format of the ALTER statement varies greatly from one RDBMS system to another. Consult your RDBMS documentation for the proper format and other parameters.

> DB2 will not allow you to DROP a column.

> DB2, MySQL, and Oracle don’t use ALTER, but use RENAME TABLE old_table_name TO new_table_name.

> PostgreSQL uses ALTER TABLE old_table_name TO new_table_name.

This starts Data Definition Language (DDL) commands. See Table Creation and SQL is DML, DDL, & DCL.
While a DataBase Administrator (DBA) will normally perform the DDL and DCL functions, you need to know how they work so that you can convey your needs to the DBA.As previously described, a database table is a two-dimensional array of columns (or attributes) and rows (or records).
You use a CREATE TABLE comand to specify the name and data type of each column.

Format:

          CREATE TABLE table_name (
                 column_name1 data_type1 [col_constraint1],
                 column_name2 data_type2 [col_constraint2],
                 ...
                 [CONSTRAINT constraint_name1 table_constraint1,]
                 [CONSTRAINT constraint_name2 table_constraint2,]
                 ...
                 );

Once the table is created, you can start loading it with data. This is done with the INSERT statement described earlier. ALTER TABLE changes the attributes of the table and DROP TABLE will remove the table definition.

As you design your tables, you will need to go through table normalization which is a way of structuring your tables so that updates do not introduce abnormalities.

The column_name must be a valid identifier according to the rules of each RDBMS.
The specific data_type associated with the column name may contain a length according to the RDBMS.
The constraint specifies constraints on the data such as NOT NULL and DEFAULT.

The format is:

          [CONSTRAINT table_constraint_name]
            CHECK (condition)

The CHECK can be defined as a column constraint:

          CREATE TABLE      EMPLOYEE_TBL (
                 ...
                 SEX        CHAR(1)   CHECK (IN ('M', 'F')),
                 ... );

The CHECK can be defined as a named table constraint:

          CREATE TABLE EMPLOYEE_TBL (
                 ...
                 SEX        CHAR(1)    NOT NULL,
                 ...
                 CONSTRAINT SEX_CHK
                      CHECK (SEX IN ('M', 'F')),
                 ... );

                                            or

          CREATE TABLE EMPLOYEE_TBL (
                 ...
                 BADGE      CHAR(4) NOT NULL,
                 ...
                 CONSTRAINT BADGE_CHK
                      CHECK (
                      (SUBSTRING(BADGE FROM 1 FOR 1) = 'E')
                      AND
                      (CAST(SUBSTRING(BADGE FROM 2 FOR 3)
                      AS INTEGER(BETWEEN 0 AND 999))
                 ...        );

 

> If the CHECK condition is violated, the INSERT or UPDATE will not happen and an error condition will be returned.

> The SQL standard says that CHECK can not be used with CURRENT_DATETIME and CURRENT_USER.
Access, Postgre SQL, and SQL Server can CHECK these values.

> MySQL only handles CHECK constraints, but it can not handle named CHECK constraints.

A CHECK constraint makes sure that a column or columns have limited values.

> A table can have zero, one, or many CHECK constraints. It uses the reserved word CHECK in the CREATE TABLE statement.

> A simple constraint (one column) can be defined as a column constraint or a table constraint.
As a simple column constraint, CHECK is placed in the column definition.

> A composite constraint (multiple columns) can only be defines as a table constraint.
As a table constraint, the CHECK definition has the column names specified.

> A CHECK column constraint works just like a WHERE clause with BETWEEN, IN, IS NULL, or LIKE.
It can allow only specific values, minimum or maximum values, or a range of values.
If you apply a CHECK constraint after the table has been populated with rows, you must ensure that the values in the table fall within the CHECK constraint values.
If the CHECK constraint is in place before the table is populated, the constraint will ensure proper values.

> General practice is to use a CONSTRAINT clause to explicitly name all constraints.
This gives a constraint name to make maintenance easier.

The format is:

          [CONSTRAINT table_constraint_name]
            UNIQUE (unique_columns)

The UNIQUE can be defined as a column constraint:

          CREATE TABLE      EMPLOYEE_TBL (
                 ...
                 BADGE_ID   CHAR(4)   NOT NULL UNIQUE,
                 ...        );

The UNIQUE can be defined as a named table constraint:

          CREATE TABLE EMPLOYEE_TBL (
                 ...
                 BADGE_ID   CHAR(4)   NOT NULL,
                 ...
                 CONSTRAINT EMPLOYEE_UNQ1
                     UNIQUE (BADGE_ID),
                 ... );

                                               or

          CREATE TABLE      SAMPLE_TBL (
                 SAMPLE1    CHAR(4)   NOT NULL,
                 SAMPLE2    CHAR(4)   NOT NULL,
                 ...
                 CONSTRAINT SAMPLE_UNQ2
                     UNIQUE (SAMPLE1, SAMPLE2)
                 ... );

The UNIQUE constraint makes sure that a column or columns have no duplicate values.> A table can have zero, one, or many UNIQUE constraints. It uses the reserved word UNIQUE in the CREATE TABLE statement.

> A simple constraint (one column) can be defined as a column constraint or a table constraint. As a simple column constraint, UNIQUE is placed in the column definition.

> A composite constraint (multiple columns) can only be defined as a table constraint. As a table constraint, the UNIQUE definition has the column names specified.

> A UNIQUE column can forbid nulls with NOT NULL.

> General practice is to use a CONSTRAINT clause to explicitly name all constraints. This gives a constraint name to make maintenance easier.

You can define the actions performed by SQL when you UPDATE or DELETE a key value in the parent table. This ON UPDATE or ON DELETE clause is placed in the FOREIGN KEY constraint.

          [CONSTRAINT  column_constraint_name]
            REFERENCES parent_table[(parent_columns)]
            [ON UPDATE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]
            [ON DELETE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]

                                                                              or

          [CONSTRAINT  table_constraint_name]
            FOREIGN KEY (key_columns)
            REFERENCES parent_table[(parent_columns)]
            [ON UPDATE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]
            [ON DELETE {NO ACTION | CASCADE | RESTRICT | SET NULL | SET DEFAULT}]

ON UPDATE:

NO ACTION   Generates an error on a foreign key violation. This is the default.

CASCADE     Updates the dependent foreign key value to the parent key value.

RESTRICT    Does not allow the dependent foreign key to change value.

SET NULL    Sets the dependent foreign key values to null.

SET DEFAULT Sets the dependent foreign key values to their default values.

ON DELETE:

NO ACTION   Generates an error on a foreign key violation. This is the default.

CASCADE     Deletes the rows that contain foreign key values that match the deleted parent key value.

RESTRICT    Does not allow the dependent foreign key to change value.

SET NULL    Sets the dependent foreign key values to null.

SET DEFAULT Sets the dependent foreign key values to their default values.

The format is:

          [CONSTRAINT  column_constraint_name]
            REFERENCES parent_table[(parent_columns)]

or

          [CONSTRAINT    table_constraint_name]
            FOREIGN KEY  (key_columns)
              REFERENCES parent_table[(parent_columns)]

You can omit the parent_column if the parent_column is the primary key of the parent_table. However, this is not recommended.
The foreign key can be defined as a column constraint:

          CREATE TABLE     PAYROLL_TBL (
                 EMPL_ID   CHAR(4) NOT NULL
                           REFERENCES EMPLOYEE_TBL(EMPL_ID),
                 ...       );

The foreign key can be defined as a named table constraint:

          CREATE TABLE PAYROLL_TBL (
                 EMPL_ID        CHAR(4) NOT NULL,
                 ...
                 CONSTRAINT     PAYROLL_FK1
                    FOREIGN KEY (EMPL_ID)
                     REFERENCES EMPLOYEE_TBL(EMPL_ID),
                 ...            );

                                                            or

          CREATE TABLE SAMPLE_TBL (
                 SAMPLE1_ID     CHAR(4) NOT NULL,
                 SAMPLE2_ID     CHAR(4) NOT NULL,
                 ...
                 CONSTRAINT     SAMPLE_FK
                    FOREIGN KEY (SAMPLE1_ID, SAMPLE2_ID)
                     REFERENCES PARENT_TBL(PARENT1_ID, PARENT2_ID),
                 ...            );

Here is how a foreign key works (so that orphan rows can not happen):
When inserting a new row in the foreign key table:
The DBMS system makes sure that the new FOREIGN KEY value matches a UNIQUE or PRIMARY KEY value in the parent table. If there is no match, the new row in the foreign key table will not be inserted.

When updating an existing row in the foreign key table:
The DBMS system makes sure that the updated FOREIGN KEY value matches a UNIQUE or PRIMARY KEY value in the parent table. If there is no match, the existing row in the foreign key table will not be updated.

When deleting an existing row in the foreign key table:
There is no need for a referential integrity check.
When inserting a new row in the parent table:
There is no need for a referential integrity check.

When updating an existing row in the parent table:
The DBMS system makes sure that none of the FOREIGN KEY values match a UNIQUE or PRIMARY KEY value in the child table. If a match exists, the existing row in the parent key table will not be updated. However, if ON UPDATE CASCADE is specified, the parent row and all matching foreign key rows will be updated with the new key values.

When deleting an existing row in the parent table:
The DBMS system makes sure that none of the FOREIGN KEY values match a UNIQUE or PRIMARY KEY value in the child table. If a match exists, the existing row in the parent key table will not be deleted. However, if ON UPDATE CASCADE is specified, the parent row and all matching foreign key rows will be deleted.