---

Add/delete an attribute to/from a relation

---

• Adding a new attribute.
  • The alter table ... ADD [COLUMN] command is used to add an attribute to an existing relation:

    ALTER TABLE table_name ADD [COLUMN] attr_name attr_type ;

  • Example:

    CREATE TABLE test1 ( ssn CHAR(9) NOT NULL, dno INTEGER ); INSERT INTO test1 values('111111111', 5); SELECT * FROM test1; SSN DNO --------- ---------- 111111111 5 ALTER TABLE test1 ADD salary DEC(8,2); SELECT * FROM test1; SSN DNO SALARY --------- ---------- ---------- 111111111 5 NULL

  • Note:

    If the relation contains tuples, then the new (added) attribute will be given the NULL value in all tuples

  ---

  ---

  ---

  ---

  ---

  ---

• Removing an existing attribute and its effect on referential integrity constraints.
  • The alter table ... DELETE command is used to:

    delete an attribute from a relation (table)

    ---

    ---

  • However:

    Deleting an attribute (column) from a relation can be very complicated !!!

    Reason:

    Deleting an attribute can compromise (= void) an Referential integrity constraint !!!

    ---

  • Example:

    The following diagram shows the foreign keys and referential constriants in the Company database If we delete the attribute ssn in the employee relation, then the referential integrity constraints: dependent(essn) → employee(ssn) works_on(essn) → employee(ssn) department(mgr_ssn) → employee(ssn) will all be compromised (because the SSN attribute in employee has been deleted !!!)

    ---

    ---

    ---

    ---

  • drop RESTRICTED:    

    Delete a tuple only if the deletion will not result in compromising of a referential integrity constraint

    SQL syntax:

    (Official SQL syntax) ALTER TABLE table_name DROP [COLUMN] attr_name RESTRICTED ;

    The attribute attr_name is dropped from relation table_name only if the attribute attr_name is NOT a part of a foreign key in some other relation

    ---

    ---

  • Note:

    The "RESTRICTED" syntax is implemented differently in MySQL.... See below for details....

    ---

    ---

    ---

    ---

  • drop CASCADE:    

    Delete the attribute. Delete the attribute from every relation that references the deleted attribute in a foreign key constraint

    SQL syntax:

    (Official SQL syntax) ALTER TABLE table_name DROP [COLUMN] attr_name CASCADE ;

    The attribute attr_name is dropped from relation table_name AND all attributes that references the deleted attribute in a foreign key constraint in other relations

  ---

  ---

  ---

  ---

• MySQL's approach to deletion without violating the Referential Integrity constraint

  • In mySQL:

    You cannot delete an (primary key) attribute that is being referenced by a foreign key constraint

    ---

    ---

  • To avoid creating the situation where you reference a "non-existing" tuple, you have TWO options when you delete a tuple that results in a violation of integrity constraints:

    Forbid the deletion the tuple (i.e.: do not perform the deletion) Delete the tuple(s)           and then   also Delete all the other tuples that reference the deleted tuple(s)

  • The syntax for restricted/cascade drop in mySQL is as follows:

    [CONSTRAINT [symbol]] FOREIGN KEY [index_name] (index_col_name, ...) REFERENCES tbl_name (index_col_name,...) [ON DELETE reference_option] [ON UPDATE reference_option] reference_option: RESTRICT | CASCADE | SET NULL | NO ACTION

    Example: cascaded drop in MySQL

    CREATE TABLE parent ( name char(10), CONSTRAINT C1 PRIMARY KEY (name) ) ; CREATE TABLE child ( name CHAR(10), p_name CHAR(10), CONSTRAINT C2 FOREIGN KEY (p_name) REFERENCES parent(name) ON DELETE CASCADE ) ; insert into parent values ('John'); insert into parent values ('Mary'); insert into child values ('John Jr', 'John'); insert into child values ('Jake', 'John'); insert into child values ('Jack', 'John'); insert into child values ('Mona', 'Mary'); insert into child values ('Molly', 'Mary'); delete from parent where name='John'; // Will delete all children from John too !!