Adding a Comment to a Column When Creating a Table in SQL

1. Introduction

In SQL, adding comments while creating a table is perhaps the best approach for describing what each column would do. This is helpful for the teams working with complex or large databases, since comments clarify the intent behind each data field, eliminating the need for any external documentation.

In this tutorial, we’ll walk through adding comments to columns when creating tables in three major database management systems: MySQL, PostgreSQL, and SQL Server.

Moreover, the examples discussed in this guide are based on the Baeldung University database schema.

2. Adding a Comment When Creating a Table

Let’s review how we can add column comments when creating tables in each database management system.

2.1. MySQL

In MySQL, we use the COMMENT keyword within the CREATE TABLE statement to add a description to each column.

To start, let’s view the general syntax:

CREATE TABLE <table_name> (
    column_name column_type COMMENT 'Comment Here'
);

Above, we specify the <table_name>, column_name, column_type, and Comment Here.

For instance, let’s create the new table Teachers, with comments added to it:

CREATE TABLE Teachers ( 
    teacher_id INT NOT NULL COMMENT 'Unique identifier for each teacher', 
    first_name VARCHAR(50) COMMENT 'first name of the teacher', 
    last_name VARCHAR(50) COMMENT 'last name of the teacher', 
    department_id INT COMMENT 'Department id describing where the teacher belongs', 
    hire_date DATE COMMENT 'the date the teacher was hired' 
);

In this example, we add five columns: teacher_id, first_name, last_name, department_id, and hire_date, each with a descriptive comment. 

Further, to view the added comments, we can run the SHOW FULL COLUMNS query in MySQL:

SHOW FULL COLUMNS FROM Teachers;

Upon running this query, we see the following output:

In this output, we can see the comments in the Comment column.

2.2. PostgreSQL

In PostgreSQL, we add comments using the COMMENT ON statement, which is executed separately after the CREATE TABLE statement:

COMMENT ON COLUMN table_name.column_name IS 'Comment Description';

In this syntax, we specify the table_name, column_name, and Comment Description.

To demonstrate, let’s create a new table Classrooms with the columns classroom_id, building_name, room_number, and, capacity:

CREATE TABLE Classrooms (
    classroom_id SERIAL PRIMARY KEY,
    building_name VARCHAR(100),
    room_number VARCHAR(10),
    capacity INT
);

Now, let’s add comments to each column of the Classrooms table:

COMMENT ON COLUMN Classrooms.classroom_id IS 'Unique identifier for each classroom';
COMMENT ON COLUMN Classrooms.building_name IS 'Building name where the classroom is located';
COMMENT ON COLUMN Classrooms.room_number IS 'Room number within the building';
COMMENT ON COLUMN Classrooms.capacity IS 'Maximum number of students the classroom can accommodate';

Here, we added clear and meaningful descriptions to each column, including classroom_id, building_name, room_number, and, capacity.

2.3. SQL Server

Unlike MySQL or PostgreSQL, adding comments to columns isn’t as easy in SQL Server. SQL Server doesn’t support inline comments within the CREATE TABLE statement. Instead, we use the sp_addextendedproperty stored procedure to add comments, often referred to as extended properties.

To begin with, let’s look at the syntax for sp_addextendedproperty:

EXEC sp_addextendedproperty 
    @name = N'Description', 
    @value = N'Describe comment here', 
    @level0type = N'SCHEMA', @level0name = 'schema_name', 
    @level1type = N'TABLE',  @level1name = 'table_name', 
    @level2type = N'COLUMN', @level2name = 'column_name';

In this syntax, we can describe the comment in the @value, schema_name (dbo for default schemas), table_name, and column_name.

For instance, let’s add the description to the name column of the existing Department table:

EXEC sp_addextendedproperty 
    @name = N'Description', 
    @value = N'Department name', 
    @level0type = N'SCHEMA', @level0name = 'dbo', 
    @level1type = N'TABLE',  @level1name = 'Department', 
    @level2type = N'COLUMN', @level2name = 'name';

We added the ‘Department name‘ as a description to the name column.

3. Conclusion

In this article, we learned how to add comments to columns when creating tables in MySQL, PostgreSQL, and SQL Server. We saw different techniques for each database management system including the COMMENT keyword in MySQL, the COMMENT ON keyword in PostgreSQL, and the sp_addextendedproperty procedure in SQL Server.