Comments on (named) constraints

Adds support for comments on named constraints, including `ForeignKeyConstraint`, `PrimaryKeyConstraint`, `CheckConstraint`, `UniqueConstraint`, solving the [Issue 5667](https://github.com/sqlalchemy/sqlalchemy/issues/5667).

Supports only PostgreSQL backend.

### Description

Following the example of [Issue 1546](https://github.com/sqlalchemy/sqlalchemy/issues/1546), supports comments on constraints. Specifically, enables comments on _named_ ones — as I get it, PostgreSQL prohibits comments on unnamed constraints.

Enables setting the comments for named constraints like this:
```
Table(
   'example', metadata,
   Column('id', Integer),
   Column('data', sa.String(30)),
   PrimaryKeyConstraint(
       "id", name="id_pk", comment="id_pk comment"
    ),
   CheckConstraint('id < 100', name="cc1", comment="Id value can't exceed 100"),
   UniqueConstraint(['data'], name="uc1", comment="Must have unique data field"),
)
```

Provides the DDL representation for constraint comments and routines to create and drop them. Class `.Inspector` reflects constraint comments via methods like `get_check_constraints` .
### Checklist
<!-- go over following points. check them with an `x` if they do apply, (they turn into clickable checkboxes once the PR is submitted, so no need to do everything at once)

-->

This pull request is:

- [ ] A documentation / typographical error fix
- [ ] A short code fix
- [x] A new feature implementation
	- Solves the issue 5667.
	- The commit message includes `Fixes: 5667`.
	- Includes tests based on comment reflection.

**Have a nice day!**

Fixes: #5667
Closes: #7742
Pull-request: https://github.com/sqlalchemy/sqlalchemy/pull/7742
Pull-request-sha: 42a5d3c3e9ccf9a9d5397fd007aeab0854f66130

Change-Id: Ia60f578595afdbd6089541c9a00e37997ef78ad3

3 files changed, 51 insertions, 0 deletions

diff --git a/lib/sqlalchemy/sql/compiler.py b/lib/sqlalchemy/sql/compiler.py
index 1ad547b79..60ec09771 100644
--- a/lib/sqlalchemy/sql/compiler.py
+++ b/lib/sqlalchemy/sql/compiler.py
@@ -5160,6 +5160,12 @@ class DDLCompiler(Compiled):
             drop.element, use_table=True
         )
 
+    def visit_set_constraint_comment(self, create, **kw):
+        raise exc.UnsupportedCompilationError(self, type(create))
+
+    def visit_drop_constraint_comment(self, drop, **kw):
+        raise exc.UnsupportedCompilationError(self, type(drop))
+
     def get_identity_options(self, identity_options):
         text = []
         if identity_options.increment is not None:
diff --git a/lib/sqlalchemy/sql/ddl.py b/lib/sqlalchemy/sql/ddl.py
index eadaa24d3..08d1072c7 100644
--- a/lib/sqlalchemy/sql/ddl.py
+++ b/lib/sqlalchemy/sql/ddl.py
@@ -793,6 +793,18 @@ class DropColumnComment(_CreateDropBase):
     __visit_name__ = "drop_column_comment"
 
 
+class SetConstraintComment(_CreateDropBase):
+    """Represent a COMMENT ON CONSTRAINT IS statement."""
+
+    __visit_name__ = "set_constraint_comment"
+
+
+class DropConstraintComment(_CreateDropBase):
+    """Represent a COMMENT ON CONSTRAINT IS NULL statement."""
+
+    __visit_name__ = "drop_constraint_comment"
+
+
 class InvokeDDLBase(SchemaVisitor):
     def __init__(self, connection):
         self.connection = connection
@@ -933,6 +945,13 @@ class SchemaGenerator(InvokeDDLBase):
                 if column.comment is not None:
                     SetColumnComment(column)._invoke_with(self.connection)
 
+            if self.dialect.supports_constraint_comments:
+                for constraint in table.constraints:
+                    if constraint.comment is not None:
+                        self.connection.execute(
+                            SetConstraintComment(constraint)
+                        )
+
         table.dispatch.after_create(
             table,
             self.connection,
diff --git a/lib/sqlalchemy/sql/schema.py b/lib/sqlalchemy/sql/schema.py
index 569603d79..4158e514b 100644
--- a/lib/sqlalchemy/sql/schema.py
+++ b/lib/sqlalchemy/sql/schema.py
@@ -2392,6 +2392,7 @@ class ForeignKey(DialectKWArgs, SchemaItem):
         link_to_name: bool = False,
         match: Optional[str] = None,
         info: Optional[_InfoType] = None,
+        comment: Optional[str] = None,
         _unresolvable: bool = False,
         **dialect_kw: Any,
     ):
@@ -2453,6 +2454,11 @@ class ForeignKey(DialectKWArgs, SchemaItem):
 
             .. versionadded:: 1.0.0
 
+        :param comment: Optional string that will render an SQL comment on
+          foreign key constraint creation.
+
+            .. versionadded:: 2.0
+
         :param \**dialect_kw:  Additional keyword arguments are dialect
             specific, and passed in the form ``<dialectname>_<argname>``.  The
             arguments are ultimately handled by a corresponding
@@ -2499,6 +2505,7 @@ class ForeignKey(DialectKWArgs, SchemaItem):
         self.initially = initially
         self.link_to_name = link_to_name
         self.match = match
+        self.comment = comment
         if info:
             self.info = info
         self._unvalidated_dialect_kw = dialect_kw
@@ -2539,6 +2546,7 @@ class ForeignKey(DialectKWArgs, SchemaItem):
             initially=self.initially,
             link_to_name=self.link_to_name,
             match=self.match,
+            comment=self.comment,
             **self._unvalidated_dialect_kw,
         )
         return self._schema_item_copy(fk)
@@ -2857,6 +2865,7 @@ class ForeignKey(DialectKWArgs, SchemaItem):
                 deferrable=self.deferrable,
                 initially=self.initially,
                 match=self.match,
+                comment=self.comment,
                 **self._unvalidated_dialect_kw,
             )
             self.constraint._append_element(column, self)
@@ -3594,6 +3603,7 @@ class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
         deferrable: Optional[bool] = None,
         initially: Optional[str] = None,
         info: Optional[_InfoType] = None,
+        comment: Optional[str] = None,
         _create_rule: Optional[Any] = None,
         _type_bound: bool = False,
         **dialect_kw: Any,
@@ -3616,6 +3626,11 @@ class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
 
             .. versionadded:: 1.0.0
 
+        :param comment: Optional string that will render an SQL comment on
+          foreign key constraint creation.
+
+            .. versionadded:: 2.0
+
         :param \**dialect_kw:  Additional keyword arguments are dialect
             specific, and passed in the form ``<dialectname>_<argname>``.  See
             the documentation regarding an individual dialect at
@@ -3639,6 +3654,7 @@ class Constraint(DialectKWArgs, HasConditionalDDL, SchemaItem):
         self._type_bound = _type_bound
         util.set_creation_order(self)
         self._validate_dialect_kwargs(dialect_kw)
+        self.comment = comment
 
     def _should_create_for_compiler(
         self, compiler: DDLCompiler, **kw: Any
@@ -3921,6 +3937,7 @@ class ColumnCollectionConstraint(ColumnCollectionMixin, Constraint):
                 _copy_expression(expr, self.parent, target_table)
                 for expr in self._columns
             ],
+            comment=self.comment,
             **constraint_kwargs,
         )
         return self._schema_item_copy(c)
@@ -4049,6 +4066,7 @@ class CheckConstraint(ColumnCollectionConstraint):
             deferrable=self.deferrable,
             _create_rule=self._create_rule,
             table=target_table,
+            comment=self.comment,
             _autoattach=False,
             _type_bound=self._type_bound,
         )
@@ -4085,6 +4103,7 @@ class ForeignKeyConstraint(ColumnCollectionConstraint):
         match: Optional[str] = None,
         table: Optional[Table] = None,
         info: Optional[_InfoType] = None,
+        comment: Optional[str] = None,
         **dialect_kw: Any,
     ) -> None:
         r"""Construct a composite-capable FOREIGN KEY.
@@ -4149,6 +4168,11 @@ class ForeignKeyConstraint(ColumnCollectionConstraint):
 
             .. versionadded:: 1.0.0
 
+        :param comment: Optional string that will render an SQL comment on
+          foreign key constraint creation.
+
+            .. versionadded:: 2.0
+
         :param \**dialect_kw:  Additional keyword arguments are dialect
           specific, and passed in the form ``<dialectname>_<argname>``.  See
           the documentation regarding an individual dialect at
@@ -4164,6 +4188,7 @@ class ForeignKeyConstraint(ColumnCollectionConstraint):
             deferrable=deferrable,
             initially=initially,
             info=info,
+            comment=comment,
             **dialect_kw,
         )
         self.onupdate = onupdate
@@ -4360,6 +4385,7 @@ class ForeignKeyConstraint(ColumnCollectionConstraint):
             initially=self.initially,
             link_to_name=self.link_to_name,
             match=self.match,
+            comment=self.comment,
         )
         for self_fk, other_fk in zip(self.elements, fkc.elements):
             self_fk._schema_item_copy(other_fk)