To create a table, use the CREATE TABLE keyword pair and then give the table a name. After that, the columns that make up the table will need to be specified in a parentheses there after. Each column will have a name, followed by its data type and length, followed by any constraints that you specify.
CREATE TABLE <table1>
(
<column1> <datatype>(<length>) <constraint1> <constraint2>,
<column2> <datatype>(<length>) <constraint1> <constraint2>,
PRIMARY KEY (<column1>),
FOREIGN KEY (<column2>) REFERENCES <table2>(<column1>)
);
You can create a table using the data from another table using the AS keyword in a CREATE TABLE statement. After the AS keyword, use a query to gather all of the information that you wish to insert into the new table. This will duplicate not only the structure of the previous table, but the data inside as well.
CREATE TABLE <table1> AS
SELECT <column1>, <column2>, ...
FROM <table2>;
In Oracle, you can create Synonyms which are objects that act as aliases for tables. When you create a Synonym, you create a name that you can use instead of the original table name when writing statements.
CREATE SYNONYM <synonymname> FOR <table1>;
Sometimes you need to change the structure of the table. The structure includes all of the columns, their respective data types, constraints and attributes. You can do this using the ALTER TABLE keyword pair.
ALTER TABLE <table1>
MODIFY COLUMN <column1> <datatype>(<length>) <constraint1> <constraint2> ...;
(MySQL/MariaDB) There is special syntax for dropping a Foreign Key or Index
ALTER TABLE <table1> DROP FOREIGN KEY <key1>;
ALTER TABLE <table1> DROP INDEX <key1>;
(Oracle) You can alter multiple columns at the same time using parentheses.
ALTER TABLE <table1>
MODIFY COLUMN (
<column1> <datatype>(<length>) <constraint1> <constraint2> ...,
<column2> <datatype>(<length>) <constraint1> <constraint2> ...
);
In Oracle, remove the COLUMN keyword after MODIFY.
ALTER TABLE <table1>
MODIFY <column1> <datatype>(<length>) <constraint1> <constraint2> ...;
The CHANGE keyword can be used to rename a column in a table:
ALTER TABLE <table1>
CHANGE <table1> <table1renamed> <datatype>(<length>);
Oracle does not have a CHANGE keyword, instead use RENAME.
ALTER TABLE <table1>
RENAME <table1> TO <table1renamed>;
This also works for columns.
ALTER TABLE <table1>
RENAME COLUMN <column1> TO <column1renamed>;
The ADD keyword can be used to add columns to a table.
ALTER TABLE <table1>
ADD <column1> <datatype>(<length>) <constraint1> <constraint2> ...;
ALTER TABLE <table1>
ADD CONSTRAINT <column1>_pk PRIMARY KEY (<column1>);
ALTER TABLE <table1>
ADD CONSTRAINT <column1>_fk FOREIGN KEY (<column1>)
REFERENCES <table2> (<column1>);
In the event that you would like to remove an object from a table, whether it be a column or a constraint, you can use the DROP keyword in an ALTER TABLE statement.
To drop a column, use DROP COLUMN.
ALTER TABLE <table1>
DROP COLUMN <column1>;
To drop more than one column at a time, omit the COLUMN keyword and use parenthesis.
ALTER TABLE <table1> DROP (<column1>, <column2>);
If you try to drop a column with an associated foreign key, the database will throw an error. This is because you cannot drop something that is tied to another table. To automatically remove the constraints from the column while dropping it, add CASCADE CONSTRAINTS.
ALTER TABLE <table1> DROP COLUMN <column1> CASCADE CONSTRAINTS;
In Oracle, MS Access, and SQL Server, the DROP CONSTRAINT keyword pair can drop anything from a Primary Key, a Foreign Key, a Check, or an attribute of a column like UNIQUE.
ALTER TABLE <table1>
DROP CONSTRAINT <constraintname>;
The name of the constraint must be known. It can be determined by describing the table structure.
In MySQL, you can still drop Primary Keys, Foreign Keys, and attributes, but they are explicitly stated in the DROP clause.
ALTER TABLE <table1> DROP PRIMARY KEY;
ALTER TABLE <table1> DROP FOREIGN KEY <keyname>;
ALTER TABLE <table1> DROP INDEX <indexname>;
ALTER TABLE <table1> DROP CHECK <checkname>;
An alternative to dropping a column in Oracle is by setting it as “unused”. Unused columns cannot be recovered. You would really only use this operation in a large data warehousing situation where the DROP COLUMN statement would use too much memory to execute. The SET UNUSED statement is speedy and will give the same effect as dropping the column, but will remain partially in memory until it is actually dropped.
ALTER TABLE <table1> SET UNUSED COLUMN <column1>;
As it sounds, this will drop all of the unused columns in a table.
ALTER TABLE <table1> DROP UNUSED COLUMNS;
If you want to remove all of the data from a table without removing the table itself, you can use TRUNCATE TABLE. This will remove all of the data, but keep the structure (columns, data types, constraints, etc.) in tact.
TRUNCATE TABLE <table1>;
So long as there are not any foreign keys tied to a table, you can remove the table from the database using DROP TABLE.
DROP TABLE <table1>;