Modifying Table using SQL

 

SQL databases organize data and relationships within tables. Sometimes, these tables also need modifications to meet new requirements. While all SQL table structures can be altered, some changes are simpler to implement than others.
There are multiple way to modify a table :

1. Adding a Column The most easiest one can alter from a table is to add a new field to the table. This is generally a very simple and risk operation as it doesn’t have any dependency on the other components within the database.

Syntax: ALTER TABLE NamaTabel ADD NamaKolom TipeData;

2. Modifying a Column To change the data type, size, or constraints of an existing column, the MODIFY or ALTER COLUMN clause is employed, depending on the SQL dialect. It’s important to note that some modifications may be restricted based on the existing data within the column.

Syntax: ALTER TABLE NamaTabel ALTER COLUMN NamaKolom TipeDataBaru;

Example: ALTER TABLE Kelas ALTER COLUMN umur VARCHAR(2);

3. Deleting a Column To remove an unnecessary column from a table, the DROP COLUMN clause is used. This action is irreversible; once a column is dropped, all data contained within it is permanently deleted.

Syntax: ALTER TABLE NamaTabel DROP COLUMN NamaKolom;

Example: Removing the Email column from the Customers table: ALTER TABLE Mobil DROP COLUMN Banyak_Ban;

4. Renaming a Column To change the name of an existing column, the RENAME COLUMN clause is utilized. The exact syntax can vary between different SQL databases.

Syntax: ALTER TABLE NamaTabel RENAME COLUMN NamaLama TO NamaBaru;

Example: ALTER TABLE Rumah RENAME COLUMN Luas TO Besar;

5. Adding Constraints Constraints are the rules at the table level, ensuring the accuracy and reliability of the data. Common constraints include that are being used to make one unique or become the main way to distinct one table from another.

Syntax: ALTER TABLE NamaTabel ADD CONSTRAINT NamaCostraint TipeCosntraint (NamaKolom);

Example: ALTER TABLE Pekerja ADD CONSTRAINT UNIQUE_EMAIL UNIQUE (Email); This make all values in the Email column unique across the Employees table.

6. Dropping Constraints To remove an existing constraint from a table, the DROP CONSTRAINT clause is used. The specific syntax may vary depending on the database system.

Syntax:
ALTER TABLE NamaTabel DROP CONSTRAINT NamaConstraint;

Example: Dropping the UNIQUE_EMAIL constraint from the Mobil table: ALTER TABLE Mobil DROP CONSTRAINT UNIQUE_EMAIL;
This removes the uniqueness requirement from the Email column, allowing duplicate entries.

7. Renaming a Table If you are unhappy with the name and want to change the already created table name. To change the name of a table, use RENAME TO.

Syntax: ALTER TABLE NamaTabelLama RENAME TO NamaTabelBaru;

Example: Renaming the Customers table to Clients: ALTER TABLE Mobil RENAME TO Mobilku;

8. Truncating a Table To remove all data from a table without deleting the table itself, the TRUNCATE TABLE statement is used. This operation is faster than deleting all rows individually and resets any auto-increment counters.

Syntax: TRUNCATE TABLE NamaTabel;
Example: TRUNCATE TABLE Sales;
This command deletes all rows in the Sales table, effectively resetting it while preserving its structure for future data.

9. Setting Unused Columns If you don’t want to delete you can instead of deleting a column, you can hide it, which is faster for big tables.

Syntax : ALTER TABLE NamaTable SET UNUSED (NamaKolom);

Example : ALTER TABLE Kelas SET UNUSED (TanggalKelas);

DBMS : Oralce APEX

Link Infographic: https://www.canva.com/design/DAGgqwYZYOI/80A8ctG0mzjrK4b1bkijqg/edit?utm_content=DAGgqwYZYOI&utm_campaign=designshare&utm_medium=link2&utm_source=sharebutton

References :

https://support.microsoft.com/en-us/office/create-or-modify-tables-or-indexes-by-using-a-data-definition-query-d935e129-229b-48d8-9f2d-1d4ee87f418e
https://study.com/academy/lesson/modifying-tables-using-sql.html
https://yasirutomo.com/sql-tutorial-ddl-create-alter-dan-drop-database-table/

Editor : Edi Purnomo Putra