[SQL 배우기] Section 8. Creating Databases and Tables

 

 

Data types in SQL:

• Boolean
  • True, False
• Character
  • char, varchar, text
• Numeric
  • integer, float
• Temporal
  • date, time, timestamp, interval
• UUID
• Array
• JSON

 

PostgreSQL: Documentation: 16: Chapter 8. Data Types

 

Primary key: a column or a group of columns used to identify a row uniquely in a table\

• represented by [PK] in pgAdmin

 

Foreign key: a field or group of fields that references the primary key of the other table

 

Constraint: rules enforced on data columns table

• prevent invalid data
• Divided into Column constraint and Table constraint:
• Common Column constraint:
  • NOT NULL constraint
  • UNIQUE constraint
  • PRIMARY Key
  • FOREIGN Key
  • CHECK constraint
    • ensures that all values in a column satisfy certain conditions
  • EXCLUSION constraint
    • ensures that if any two rows are compared on the specified column or expression using the specified operator, not all of these comparisons will return TRUE
• Common Table constraint:
  • **CHECK (**condition)
    • to check conditions when inserting or updating data
  • REFERENCES
    • to constrain the value stored in the column that must exist in a column in another table
  • UNIQUE (column_list)
    • forces the values stored in the columns listed inside the parentheses to be unique
  • PRIMARY KEY (column list)
    • allows you to define the primary key that consists of multiple columns

 

The general syntax for creating a table:

• CREATE table table_name ( column_name TYPE column_constraint, column_name TYPE column_constraint, table_constraint table_constraint ) INHERITS existing_table_name;

 

SERIAL: creates a sequence object and sets the next value generated by the sequence as the default value for the column

• perfect for PRIMARY key
• does not adjust for deletion

 

INSERT: allow to add rows to a table

• general syntax: INSERT INTO table (column1, column 2, …) VALUES (value1, value2, …), (value1, value2, …), ….;
• SERIAL columns do not need to be provided a value

 

UPDATE: allows change of values of the columns

• general syntax: UPDATE table SET column1 = value1, column2 = value2, … WHERE condition;
• without WHERE, everything is reseted
• update from another table: UPDATE tableA SET original_col = tableB.new_col FROM tableB WHERE tableA.id = tableB.id
• return affected rows UPDATE table SET column=value RETURNING column1, column2

 

DELETE: remove rows from the table

• DELETE FROM table WHERE row_id = 1
• DELETE FROM tableA USING tableB WHERE tableA.id = tableB.id

 

ALTER: allows for changes to an existing table structure

• 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

 

DROP: allows the complete removal of a column in a table

• General Syntax ALTER TABLE table_name DROP COLUMN col_name

 

CHECK: allows the creation of more customized constraints that adhere to a certain condition