Introduction to SQL: Creating Databases and Tables (Part Four)
This is the fourth introduction to learning how to program with SQL. In this article, we'll be focusing on how to create Databases and Tables.
Data Types
Boolean (True or False)
Character (char, varchar, and text)
Numeric (integer and floating-point number)
Temporal (date, time, timestamp, and interval)
UUID (Universally Unique Identifiers)
Array (Stores an array of strings, numbers, etc)
JSON
Hstore key-value pair
Special types such as network address and geometric data
When creating databases and tables, you need to carefully consider which data types you should use for the data to be stored
Link for Data Types:
https://www.postgresql.org/docs/current/datatype.html
Google search for best practices.
When creating a database and table, take the time to plan for long-term storage since you can’t in information, but you can delete information
Primary Keys and Foreign Keys
A primary key is a column or a group of columns used to identify rows unique to that table. It is important since they allow us to discern what columns should be used for joining tables together.
A foreign key is a field or group of fields in a table that uniquely identifies a row in another table. Defined in a table that references the primary key of the other table.
A table that contains a foreign key is called referencing table or a child table.
The table to which foreign key references is called a referenced table or parent table.
A table can have multiple foreign keys depending on its relationship with other tables.
When creating tables and defining columns, we can use constraints to define columns as being primary keys or attach a foreign key relationship to another table.
Constraints
Used to prevent invalid data from being entered into the database. It is a way to ensure the accuracy and reliability of the data in the database.
Two main categories:
Column Constraints (Constrains data in a column to adhere to certain conditions)
Table Constraints (applied to the entire table rather than an individual column)
Commonly used constraints:
NOT NULL constraint (Ensures column cannot have NULL value)
UNIQUE Constraint (Ensures all values in a column are different)
PRIMARY Key (Uniquely identifies each row in a database table)
FOREIGN Key (Constrain data based on columns in other tables)
CHECK Constraint (Ensures all values in the column satisfy certain conditions)
EXCLUSION Constraint (Ensures that if any two rows are compared on a specific column using a specific operator, not all of these comparisons will return TRUE)
Table Constraints
CHECK (check a condition when inserting or updating data)
REFERENCES (constrain value stored in the column that must exist in a column in another table)
UNIQUE (forces values stored in column listed inside parentheses to be unique)
PRIMARY KEY (Allows you to define a primary key that consists of multiple columns)
CREATE Keyword
General syntax:
CREATE TABLE table_name (
column_name TYPE column_constraint,
column_name TYPE column_constraint,
table_constraint table_constraint
) INHERITS existing_table_name;
Common Simple Syntax:
CREATE TABLE table_name (
column_name TYPE column_constraint,
column_name TYPE column_constraint,
);
Example syntax:
CREATE TABLE players (
player_id SERIAL PRIMARY KEY,
age SMALLINT NOT NULL,
);
* SERIAL will create a sequence object and set the next value generated by sequence as the default value for a column. Perfect for the primary key, because it logs unique integer entries for you automatically upon insertion.
Image example:
In the image above I have created a table for account. user_id, username, email, password, created_on, and last_login are all created within the Columns (as you can see on the left)
Image example 2:
In this image, I have created an account_job which contains user_id, job_id, and hire_date (appears on the left)
PS. I have also created a job table (if you’re wondering why there is a ‘job’ there)
INSERT
Allows you to add rows to a table.
General syntax:
INSERT INTO table (column1,column2, …)
VALUES
(value1,value2,…),
(value1,value2,…) ,…;
Syntax for inserting Values from another table:
INSERT INTO table (column1,column2, …)
SELECT column1,column2,…
FROM another_table
WHERE condition;
The inserted values must match up with the table including constraints
SERIAL columns do not need to be provided a value
Image example:
UPDATE
Allows changing of values of the column in a table.
General syntax:
UPDATE table
SET column1 = value1,
column2 = value2 ,…
WHERE
condition;
Example syntax:
UPDATE account
SET last_login = CURRENT_TIMESTAMP
WHERE last_login IS NULL
Reset everything without WHERE condition:
UPDATE account
SET last_login = CURRENT_TIMESTAMP
Image example:
Set based on another column:
UPDATE account
SET last_login = created_on
Image example:
Use another table’s values (UPDATE join):
UPDATE TableA
SET original_col = TableB.new_col
FROM tableB
WHERE tableA.id = TableB.id
Image example:
Image example (result):
Should be the same as created_on
Return affected rows:
UPDATE account
SET last_login = created_on
RETURNING account_id,last_login
Image example:
DELETE
Use the DELETE clause to remove rows from a table
Example syntax:
DELETE FROM table
WHERE row_id = 1
Image example before deletion:
Image example after using DELETE:
Can also delete rows based on their presence in other tables
Example syntax:
DELETE FROM tableA
USING tableB
WHERE tableA.id = TableB.id
If you want to delete all rows from a table
Example syntax:
DELETE FROM table
* You can also add a returning call to return rows that were removed.
ALTER
The ALTER clause allows for changes to an existing table structure, such as
Adding, dropping, or renaming columns
Changing a column’s data type
Set DEFAULT values for a column
Add CHECK constraints
Rename table
General syntax:
ALTER TABLE table_name action
Renaming table:
ALTER TABLE table_name
RENAME TO new_table_name
Renaming column:
ALTER TABLE table_name
RENAME COLUMN column_name TO new_column_name
Adding columns:
ALTER TABLE table_name
ADD COLUMN new_col TYPE
Removing columns:
ALTER TABLE table_name
DROP COLUMN col_name
Alter constraints:
ALTER TABLE table_name
ALTER COLUMN col_name
SET DEFAULT value
SET DEFAULT value can be changed to “DROP DEFAULT”, “SET NOT NULL”, “DROP NOT NULL”, or “ADD CONSTRAINT constraint_name”. Up to you to decide what you want to use.
Link to check out all ALTER TABLE:
https://www.postgresql.org/docs/current/sql-altertable.html
This concludes part four of SQL!
Part Five: Introduction to SQL: Creating Databases and Tables (Part Five)