[go: up one dir, main page]
Scribd
Upload
37 views3 pages

Create Table: 5.5.1. Adding A Column

The document discusses how to create, alter, and drop databases and tables in SQL. It describes how to create a new database and tables using CREATE DATABASE and CREATE TABLE. It also covers how to remove tables using DROP TABLE. The document then discusses various ways to alter tables, including adding and removing columns, adding and removing constraints, changing column defaults, data types, and renaming columns and tables, all using the ALTER TABLE command.

Uploaded by

MuhammadWahyuTP
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
37 views3 pages

Create Table: 5.5.1. Adding A Column

The document discusses how to create, alter, and drop databases and tables in SQL. It describes how to create a new database and tables using CREATE DATABASE and CREATE TABLE. It also covers how to remove tables using DROP TABLE. The document then discusses various ways to alter tables, including adding and removing columns, adding and removing constraints, changing column defaults, data types, and renaming columns and tables, all using the ALTER TABLE command.

Uploaded by

MuhammadWahyuTP
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 3

To create a new database:

CREATE DATABASE baru;

To create a table, you use the aptly named CREATE TABLE command. For example:
CREATE TABLE my_first_table (
first_column text,
second_column integer );

This creates a table named my_first_table with two columns. The first column is
named first_column and has a data type of text; the second column has the
name second_column and the type integer.
CREATE TABLE products (
product_no integer,
name text,
price numeric );

If you no longer need a table, you can remove it using the DROP TABLE command. For
example:
DROP TABLE my_first_table;
DROP TABLE products;

All these actions are performed using the ALTER TABLE command,
5.5.1. Adding a Column
To add a column, use a command like:
ALTER TABLE products ADD COLUMN description text;

The new column is initially filled with whatever default value is given (null if you don't
specify a DEFAULT clause).

You can also define constraints on the column at the same time, using the usual syntax:
ALTER TABLE products ADD COLUMN description text CHECK (description <> '');

5.5.2. Removing a Column


To remove a column, use a command like:
ALTER TABLE products DROP COLUMN description;

Whatever data was in the column disappears. Table constraints involving the column are
dropped, too. You can authorize dropping everything that depends on the column by
adding CASCADE:
ALTER TABLE products DROP COLUMN description CASCADE;

5.5.3. Adding a Constraint


To add a constraint, the table constraint syntax is used. For example:
ALTER TABLE products ADD CHECK (name <> '');
ALTER TABLE products ADD CONSTRAINT some_name UNIQUE (product_no);
ALTER TABLE products ADD FOREIGN KEY (product_group_id) REFERENCES
product_groups;

To add a not-null constraint, which cannot be written as a table constraint, use this syntax:
ALTER TABLE products ALTER COLUMN product_no SET NOT NULL;

The constraint will be checked immediately, so the table data must satisfy the constraint
before it can be added.
5.5.4. Removing a Constraint
To remove a constraint you need to know its name. If you gave it a name then that's easy.
Then the command is:

ALTER TABLE products DROP CONSTRAINT some_name;

This works the same for all constraint types except not-null constraints. To drop a not null
constraint use:
ALTER TABLE products ALTER COLUMN product_no DROP NOT NULL;

5.5.5. Changing a Column's Default Value


To set a new default for a column, use a command like:
ALTER TABLE products ALTER COLUMN price SET DEFAULT 7.77;

Note that this doesn't affect any existing rows in the table, it just changes the default for
future INSERT commands.
To remove any default value, use:
ALTER TABLE products ALTER COLUMN price DROP DEFAULT;

5.5.6. Changing a Column's Data Type


To convert a column to a different data type, use a command like:
ALTER TABLE products ALTER COLUMN price TYPE numeric(10,2);

5.5.7. Renaming a Column


To rename a column:
ALTER TABLE products RENAME COLUMN product_no TO product_number;

5.5.8. Renaming a Table


To rename a table:
ALTER TABLE products RENAME TO items;

You might also like