---

Modifying existing relations: Altering and Dropping

---

• Modifying the structure of an existing relation
  • Important note:

    After you have defined a relation (using the CREATE TABLE command), you can still make changes to its definition However: The changes will be more complicated/difficult to make when a table already contains some tuples (= data)

    ---

    ---

  • Commands to change the tables in a database:

    DROP TABLE : delete a table from the database schema         NOTE: ALL tuples of the relation (= table) will be deleted !!!          The ALTER TABLE command allows you to: add/delete one attribute to/from a relation add/delete one named constraint to/from a relation          NOTE: The newly added attribute will have a NULL value in every tuple of the relation (= table)

  ---

  ---

  ---

  ---

  ---

  ---

• DROP TABLE
  • Deleting table from the database schema:

    DROP TABLE relation_name

  • The relation definition is removed, along with any tuples (data) that you have previously inserted....

  • NOTE: Use this command with ultmost care .... if you make a mistake, it may be impossible to recover the data !

  ---

  ---

  ---

  ---

  ---

  ---

• ALTER TABLE: changing a relation schema.
  • ALTER TABLE command is used to:

    add one attribute to a relation remove one attribute from a relation add one constraint to a relation remove one constraint from a relation

    The ALTER TABLE command is discussed in the next webpage

  ---

  ---

  ---

  ---

• Renaming an attribute
  • Renaming an attribute:

    Renaming attribute is not available in standard SQL

    Some database system can implement its own "rename" command

    ---

    ---

  • How to "rename" an attribute using standard SQL:

    Drop the attribute and then add the attribute back with a different name..

    BUT.. in doing so, you will lose all the values of the dropped attribute !!!

    ---

  • How to rename an attribute:

    Create a new relation with the desired attribute name Use a INSERT command to copy the data from the old relation into the newly created relation Then drop the old relation table.