Note_Tech

All technological notes.

---

Project maintained by simonangel-fong Hosted on GitHub Pages — Theme by mattgraham

SQL - Modify Table

Back

• SQL - Modify Table
  • ALTER TABLE
  • Rename a table
  • Change Column
  • Change Constraint
    • Example

---

ALTER TABLE

• Allow for changes to an existing table structure:

  • adding, droping, or renaming columns
  • change a column’s data type
  • Set DEFAULT values for a column
  • Add CHECK constraints
  • Rename table

---

Rename a table

• Syntax:

-- rename a table
ALTER TABLE tb_name
RENAME TO new_tb_name;

---

Change Column

-- rename a column
ALTER TABLE tb_name
RENAME COLUMN old_col_name TO new_col_name;

-- Adding Columns
ALTER TABLE tb_name
ADD COLUMN new_col_name datetype;

-- drop single column
ALTER TABLE tb_anem
DROP COLUMN col_name;

-- drop multipl column
ALTER TABLE tb_anem
DROP COLUMN col_1
DROP COLUMN col_2;

-- remove all dependencies
ALTER TABLE tb_name
DROP COLUMN col_name CASCADE;

-- check existence before dropping
ALTER TABLE tb_name
DROP COLUMN IF EXISTS col_name;

• DROP COLUMN

  • Allow for complete removal of a column in a table.

  • In PostgreSQL, this will also automatically remove all of its indexes and constraints involving the column.

  • However, it will not remove columns used in views, triggers, or stored procedures without the additional CASCADE clause.

---

Change Constraint

-- add constraints
ALTER TABLE tb_name
ALTER COLUMN col_name
ADD CONSTRAINT constraint_name;

-- set default
ALTER TABLE tb_name
ALTER COLUMN col_name
SET DEFAULT value

-- drop default
ALTER TABLE tb_name
ALTER COLUMN col_name
DROP DEFAULT;

-- set Not null
ALTER TABLE tb_name
ALTER COLUMN col_name
SET NOT NULL;

-- drop Not null
ALTER TABLE tb_name
ALTER COLUMN col_name
DROP NOT NULL;

---

Example

CREATE TABLE information(
	info_id	SERIAL PRIMARY KEY,
	title	VARCHAR(500)	NOT NULL,
	person	VARCHAR(50)		NOT NULL UNIQUE
)

-- rename table
ALTER TABLE information
RENAME TO new_info;

-- rename a column
ALTER TABLE new_info
RENAME COLUMN person TO people;

-- drop column
ALTER TABLE new_info
DROP COLUMN IF EXISTS people;

---

TOP