Changing Table Structure All the commands above change the data in the database. This command changes the database’s underlying structure. Alter Table TABLENAME clauses designates what kind of changes to make. Clauses Note: multiple (described next) can be added to clauses one command, separated by commas alter Example: Alter Table TABLENAME Add country varchar(30), Drop continent, Change postalCode zipCode char(5); Add the field to clause Airline Add FebTrips (T15) Allows you to add a new field to the table fieldname fieldtype Add Use same field types as in command Create Delete column Airline Drop columnname Removes a field from the table If the column is a primary key, have to remove you’ll the primary key constraint (see below) before you’ll be able to drop the column. Note: you can drop (alter actually) multiple columns by repeating the action alter table tblName drop column1, drop column2, drop column3; Change the type of Modify fieldname newtype [Not Null] expenses to Dec 5,2 Modifies the type of a field Optionally, you can also designate the field as not null Change the name of the Change colname newcolname coltype employeeId to empId Changes the name of a column Remove the keys from Removing a primary key tblFebTrips Alter Table tablename Drop Primary Key Removes all keys from a table Restore keys Add a primary key to a table Alter Table tablename Add Primary Key (fieldnames) Parenthesis are required
Changing Table Structure All the commands above change the data in the database. This command changes the database’s underlying structure. Alter Table TABLENAME clauses Clauses designates what kind of changes to make. Note: multiple clauses (described next) can be added to one alter command, separated by commas Example: Alter Table TABLENAME Add country varchar(30), Drop continent, Change postalCode zipCode char(5); Add clause Allows you to add a new field to the table Add fieldname fieldtype Use same field types as in Create command Add the Airline field to FebTrips (T15) Drop columnname Removes a field from the table If the column is a primary key, you’ll have to remove the primary key constraint (see below) before you’ll be able to drop the column. Note: you can drop (alter actually) multiple columns by repeating the action alter table tblName drop column1, drop column2, drop column3; Delete column Airline Modify fieldname newtype [Not Null] Modifies the type of a field Optionally, you can also designate the field as not null Change the type of expenses to Dec 5,2 Change colname newcolname coltype Changes the name of a column Change the name of the employeeId to empId Removing a primary key Alter Table tablename Drop Primary Key Removes all keys from a table Remove the keys from tblFebTrips Add a primary key to a table Alter Table tablename Add Primary Key (fieldnames) Parenthesis are required Restore keys
5%20-%20Updating%20Data%20&%20Structure.docx