
Example:
Detailed Explanation:
CREATE TABLE employees
: This command tells PostgreSQL to create a new table namedemployees
.employee_id SERIAL PRIMARY KEY
:employee_id
is the name of the column.SERIAL
is a special data type that automatically generates a unique identifier for each row (an auto-incrementing integer).PRIMARY KEY
means this column uniquely identifies each row in the table.
first_name VARCHAR(50)
:first_name
is the name of the column.VARCHAR(50)
specifies that this column can store up to 50 characters.
last_name VARCHAR(50)
andemail VARCHAR(100)
follow the same pattern.
Adding Columns
To add a new column to an existing table, you use theALTER TABLE
statement.
Example:
Detailed Explanation:
ALTER TABLE employees
: This command specifies the table (employees
) to be modified.ADD COLUMN date_of_birth DATE
:ADD COLUMN
specifies that a new column is being added.date_of_birth
is the name of the new column.DATE
is the data type for the new column, which stores date values.
Defining Primary Keys
A primary key is a column or a set of columns that uniquely identifies each row in a table. It ensures that each row has a unique identifier.Adding a Primary Key During Table Creation:
Adding a Primary Key to an Existing Table:
If the table already exists without a primary key, you can add it using theALTER TABLE
statement:
Detailed Explanation:
ADD PRIMARY KEY (employee_id)
:ADD PRIMARY KEY
specifies the addition of a primary key.(employee_id)
indicates that theemployee_id
column is being set as the primary key.
Defining Foreign Keys
A foreign key is a column or a set of columns that establishes a link between the data in two tables. It ensures referential integrity by enforcing a relationship between the columns of two tables.Example:
First, create thedepartments
table:
Adding a Foreign Key to the employees
Table:
- Add the
department_id
column to theemployees
table:
- Define the foreign key relationship:
Detailed Explanation:
ALTER TABLE employees
: This command specifies the table (employees
) to be modified.ADD COLUMN department_id INTEGER
:ADD COLUMN
specifies that a new column is being added.department_id
is the name of the new column.INTEGER
is the data type for the new column, which stores integer values.
ADD CONSTRAINT fk_department FOREIGN KEY (department_id) REFERENCES departments(department_id)
:ADD CONSTRAINT fk_department
names the new constraintfk_department
.FOREIGN KEY (department_id)
specifies that thedepartment_id
column in theemployees
table is a foreign key.REFERENCES departments(department_id)
indicates that this foreign key references thedepartment_id
column in thedepartments
table.
Full Example:
Combining all the above concepts, here’s how to create both tables with the necessary columns and constraints:departments
and employees
, with the employees
table having a foreign key that references the departments
table. Each table and column is defined with appropriate data types and constraints to ensure data integrity and relationships.