1 / 20

Modifying a table structure (mysql)

Modifying a table structure (mysql). Alter Table Drop, add and modify columns Add, drop, and modify constraints Enable or disable constraints. Recreate Incomplete Test Table. Eexcute these commands: create table hf_test( id int ); drop table hf_test; create table hf_test( id int );.

adah
Download Presentation

Modifying a table structure (mysql)

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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;

More Related