Creating Databases and Tables:
We’ve focused on querying and reading data from existing databases and tables. Let’s now shift our focus to creating our own 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 should carefully consider which data types should be used for the data to be stored. Review the documentation to see limitations of data types: postgresql.org/docs/current/datatype.html
●For example : Imagine we want to store a phone number, should it be stored as numeric? If so, which type of numeric?
●Based on the limitations, you may think it makes sense to store it as a BIGINT data type, but we should really be thinking what is best for the situation. Why bother with numeric at all? We don’t perform arithmetic with numbers, so it probably makes more sense as a VARCHAR data type instead.
●In fact, searching for best practice online, you will discover its usually recommended to store as a text based data type due to a variety of issues.
○No arithmetic performed
○Leading zeros could cause issues, 7 and 07 treated same numerically, but are not the same phone number.
Primary and Foreign Keys:
●A primary key is a column or a group of columns used to identify a row uniquely in a table. Primary keys are also important since they allow us to easily discern what columns should be used for joining tables together.
●Example of Primary Key:
Notice its integer based and unique.We saw customers had a unique, non-null customer_id column as their primary key.
●A foreign key is a field or group of fields in a table that uniquely identifies a row in another table. A foreign key is defined in a table that references to the primary key of the other table. The table that contains the foreign key is called referencing table or child table. The table to which the foreign key references is called referenced table or parent table. A table can have multiple foreign keys depending on its relationships with other tables.
●Recall in the dvdrental database payment table, each payment row had its unique payment_id ( a primary key) and identified the customer that made the payment through the customer_id (a foreign key since it references the customer table’s primary key).
Example:
Primary Key for Payment Table
Multiple Foreign Key References
Note pgAdmin won’t alert you to FK
k
●You may begin to realize primary key and foreign key typically make good column choices for joining together two or more tables.
●When creating tables and defining columns, we can use constraints to define columns as being a primary key, or attaching a foreign key relationship to another table.
●Constraints:
●Constraints are the rules enforced on data columns on table.
●These are used to prevent invalid data from being entered into the database.
●This ensures the accuracy and reliability of the data in the database.
●Constraints can be divided into two main categories:
1.Column Constraints :Constrains the data in a column to adhere to certain conditions.
2. Table Constraints :applied to the entire table rather than to an individual column.
●Types of table Constraints:
1.CHECK (condition): to check a condition when inserting or updating data.
2. REFERENCES :to constrain the value stored in the column that must exist in a column in another table.
3. UNIQUE (column_list) : forces the values stored in the columns listed inside the parentheses to be unique.
4. PRIMARY KEY(column_list) : Allows you to define the primary key that consists of multiple columns.
●The most common constraints used:
- NOT NULL Constraint : Ensures that a column cannot have NULL value.
2. UNIQUE Constraint :Ensures that all values in a column are different.
3. FOREIGN Key :Constrains data based on columns in other tables.
4. PRIMARY Key :Uniquely identifies each row/record in a database table.
5. CHECK Constraint : Ensures that all values in a column satisfy certain conditions.
6. 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.
