Indexes for Constraints¶ Let us understand details related to indexes for constraints. Constraints such as primary key and unique are supported by indexes. Primary Key – Unique and Not Null. Unique – Unique and can be null. Unless data is sorted, we need to perform full table scan to enforce uniqueness. Almost all the databases …
Indexes on Tables¶ Let us go through the details related to indexes supported in RDBMS such as Postgres. An index can be unique or non unique. Unique Index – Data will be sorted in ascending order and uniqueness is enforced. Non Unique Index – Data will be sorted in ascending order and uniqueness is not …
Managing Constraints¶ Let us understand how we can manage constraints. We can add constraints while creating the tables or after creating the tables. Constraints such as NOT NULL, CHECK, FOREIGN KEY are automatically dropped when we drop the table. Even PRIMARY KEY and UNIQUE constraints are dropped if they are not used to enforce constraints. …
Different Types of Constraints¶ Let us understand details about different types of constraints used in RDBMS databases. Supported constraints: NOT NULL constraint CHECK constraint UNIQUE constraint PRIMARY KEY constraint FOREIGN KEY constraint All constraints can be added while creating the table or on pre-created tables using ALTER. Typically we define NOT NULL, CHECK constraints while …
Adding or Modifying Columns¶ Let us understand details about adding or modifying columns using ALTER TABLE command. New columns can be added to the existing table. However, if you want to add a column which cannot have null value then you need to follow these steps. Add column to the table. Update data in the …
Overview of Data Types¶ Let us get an overview of supported datatypes in Postgres. Here is the sample CREATE TABLE command for the review. CREATE TABLE users ( user_id SERIAL PRIMARY KEY, user_first_name VARCHAR(30) NOT NULL, user_last_name VARCHAR(30) NOT NULL, user_email_id VARCHAR(50) NOT NULL, user_email_validated BOOLEAN DEFAULT FALSE, user_password VARCHAR(200), user_role VARCHAR(1) NOT NULL DEFAULT …
