Modifying a table structure (mysql)

1. Modifying a table structure (mysql) • Alter Table • Drop, add and modify columns • Add, drop, and modify constraints • Enable or disable constraints

2. Recreate Incomplete Test Table Eexcute these commands: create table hf_test( id int ); drop table hf_test; create table hf_test( id int );

3. To Add A Column • Provide definition for new column • Syntax: ALTER TABLE tablename ADD (column definition ); • Note: you can add several columns at a time (comma separated)

4. Add name column Try this: alter table hf_test add ( name varchar(20) );

5. To Delete a Column • Syntax: ALTER TABLE table_name DROP COLUMN column_name; • Try: ALTER TABLE hf_test DROP COLUMN name;

6. To Modify Existing Columns Syntax: ALTER TABLE tablenameMODIFY revised column definition; // no parenthesis

7. Changing Column Size • Increase of column size is OK • To decrease column size: • Data may be truncated if needed ( save data first ) • To change data type of column: • Ok, but data will be converted to new data type  loss of information possible

8. Make name smaller Try this: alter table hf_test modify name varchar(10); Note: data may be truncated but query is executed

9. Change Data Type ALTER TABLE hf_test MODIFY name date; Note: default values for name (0000-00-00) will replace existing column values

10. Let’s go back ALTER TABLE hf_test MODIFY name varchar(20); ALTER TABLE hf_test ADD (enrolled date);

11. Add default Constraint alter table hf_test modify enrolled date default ‘2010-10-10’; To test: insert into hf_test (id, name) values ( 4, ‘Green' ); select * from hf_test;

12. More Constraint Modifications ALTER TABLE hf_testMODIFY enrolled date NOT NULL; • Null values converted to some default value (for example, empty string if varchar data type) DESCRIBE hf_test;

13. Removing NOT NULL alter table hf_test modify name date null; describe hf_test;

14. Adding a Primary Key Constraint Syntax: ALTER TABLE tablename ADD CONSTRAINT table_pk PRIMARY KEY (columnname); • Column must exist • Values should not be null (one null value possibly ok) • Values must be unique

15. Add Primary Key Try this: ALTER TABLE hf_test ADD CONSTRAINT hf_test_pk PRIMARY KEY ( id ); DESCRIBE hf_test;

16. Adding a Foreign Key Constraint Syntax: ALTER TABLE tablenameADD CONSTRAINT table_fk FOREIGN KEY ( column ) REFERENCES table2 (column); • Note: All values in current table must exist in parent table

17. Adding Foreign Key Constraints CREATE TABLE hf_test2 ( id int ); ALTER TABLE hf_test2 ADD CONSTRAINT hf_test2_fk FOREIGN KEY ( id ) REFERENCES hf_test ( id );

18. Drop Constraints ALTER TABLE tablename DROP PRIMARY KEY; ALTER TABLE hf_test DROP PRIMARY KEY;

19. Drop Constraints • ALTER TABLE tablename DROP FOREIGN KEY constraint_name; • ALTER TABLE hf_test2 DROP FOREIGN KEY hf_test2_fk;

20. Rename Tables • RENAME TABLE table1 TO table2; RENAME TABLE hf_test2 TO hf_test3;