FrançaisGet started

ALTER and DROP

Changing a schema: ALTER TABLE (add, remove, rename a column, change a type, add or remove a constraint), DROP TABLE with RESTRICT or CASCADE, and the difference between DROP, TRUNCATE and DELETE.

  • 23 cards
  • 5 topics
  • Free
Study this deck

The outline, topic by topic

Each topic is weighted the way the exam weighs it.

  1. Concepts
  2. Questions
  3. Sort and link
  4. Scenarios
  5. Understand

A few cards from the deck

  • ALTER TABLE: what is it?

    Show the answer

    Statement that changes the structure of an existing relation: a column added, removed or renamed, a type changed, a constraint added or removed.

    It evolves the schema without recreating the table or losing its rows. But the table is already filled: every change must suit the existing rows, or the database refuses it.

  • The client table holds 2,000 rows. Which clause, placed after ALTER TABLE client, adds a mandatory phone number without the database refusing?

    • ADD COLUMN telephone VARCHAR(15) NOT NULL DEFAULT ''
    • ADD COLUMN telephone VARCHAR(15) NOT NULL CHECK (telephone <> '')
    • ADD COLUMN telephone VARCHAR(15) DEFAULT ''
    • ADD COLUMN telephone VARCHAR(15) NOT NULL
    Show the answer

    ADD COLUMN telephone VARCHAR(15) NOT NULL DEFAULT ''

    The 2,000 existing rows must receive a value: without DEFAULT they would get NULL, and PostgreSQL answers column "telephone" of relation "client" contains null values. With DEFAULT but no NOT NULL, the phone would stay optional.

  • Adding a column is written ALTER TABLE client … telephone VARCHAR(15); removing it is written ALTER TABLE client … telephone.

    Show the answer

    Adding a column is written ALTER TABLE client ADD COLUMN telephone VARCHAR(15); removing it is written ALTER TABLE client DROP COLUMN telephone.

    Removing a column also deletes all the values it held, with no way back outside a transaction.

  • DROP TABLE: what is it?

    Show the answer

    Statement that removes a whole relation: its rows, columns, indexes and constraints all disappear with it.

    Afterwards the table no longer exists: a SELECT answers relation "client" does not exist. By default it refuses to remove a table that other objects depend on.

  • In PostgreSQL, a nom VARCHAR(200) column holds names of 3 to 25 characters. ALTER TABLE client ALTER COLUMN nom TYPE VARCHAR(20) is run. What happens?

    • The ALTER passes and the too-long names are cut to 20 characters
    • The ALTER passes: only future INSERTs will be checked
    • The ALTER passes and the too-long rows are deleted
    • The ALTER fails and changes nothing: one value exceeds 20 characters
    Show the answer

    The ALTER fails and changes nothing: one value exceeds 20 characters

    PostgreSQL does not cut the existing values silently: it answers value too long for type character varying(20) and the table stays as it was. The too-long names have to be fixed first.

Study this deck