SQL Named Constraint

SQL supports following constraints:

• CHECK - Ensures that the value in a column meets a specific condition
• DEFAULT - Specifies a default value when specified none for this column
• NOT NULL - Indicates that a column cannot store NULL value
• FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
• PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
• UNIQUE - Ensures that each row for a column must have a unique value

Starting with an example of having named constraint in CREATE statement.

CREATE TABLE [dbo].[Bar]
(
    [Id] int CONSTRAINT [CO_Bar_Id_NOTNULL] NOT NULL, -- PRIMARY KEY,
    [Name] NVARCHAR(50) -- CONSTRAINT [CO_Bar_Name_NOTNULL] NOT NULL UNIQUE, 
  --CONSTRAINT [PK_Bar_Id] PRIMARY KEY ([Id]), -- named constraint
    CONSTRAINT [CO_Bar_Name_UNIQUE] UNIQUE ([Name]),
)
GO

CREATE TABLE [dbo].[FooType]
(
    [Id] int CONSTRAINT [CO_FooType_Id_NOTNULL] NOT NULL, -- PRIMARY KEY,
    [Name] NVARCHAR(50) CONSTRAINT [CO_FooType_Name_NOTNULL] NOT NULL UNIQUE, 
    CONSTRAINT [PK_FooType_Id] PRIMARY KEY ([Id]), -- named constraint
)
GO

CREATE TABLE [dbo].[Foo]
(
    [Id] int CONSTRAINT [CO_Foo_Id_NOTNULL] NOT NULL,
    [Name] NVARCHAR(50) CONSTRAINT [CO_Foo_Name_NOTNULL] NOT NULL, 
    [Description] NVARCHAR(max) NULL, 
    [LinkedBarId] int CONSTRAINT [CO_Foo_LinkedBarId_NOTNULL] NOT NULL, 
    [TypeId] int, -- CONSTRAINT [CO_Foo_TypeId_NOTNULL] NOT NULL, 
    CONSTRAINT [PK_Foo] PRIMARY KEY ([Id]),
    CONSTRAINT [FK_Foo_TypeId]
        FOREIGN KEY ([TypeId])
        REFERENCES [dbo].[FooType] ([Id]) 
        ON DELETE CASCADE
        ON UPDATE CASCADE,
  --CONSTRAINT [FK_Foo_LinkedBarId] -- named FOREIGN KEY constraint
  --FOREIGN KEY ([LinkedBarId])
  --REFERENCES [dbo].[Bar] ([Id]) 
  --    ON DELETE CASCADE
  --    ON UPDATE CASCADE,
)
GO

Separated from CREATE statement/batch, a constraint can be added in ALTER statement.

ALTER TABLE [dbo].[Bar]
ADD CONSTRAINT [PK_Bar_Id] PRIMARY KEY ([Id])
GO

ALTER TABLE [dbo].[Bar]
ADD CONSTRAINT [CO_Bar_Name_NOTNULL] -- named NOT NULL constraint
CHECK([Name] is NOT NULL)
GO

ALTER TABLE [dbo].[Foo]
ADD CONSTRAINT [DF_Foo_Description_NA] -- named DEFAULT constraint
DEFAULT ('N/A') FOR [Description]
GO

ALTER TABLE [dbo].[Foo]
ADD CONSTRAINT [CO_Foo_TypeId_NOTNULL] -- named NOT NULL constraint
CHECK([TypeId] is NOT NULL)
GO

ALTER TABLE [dbo].[Foo]
ADD CONSTRAINT [FK_Foo_LinkedBarId] -- named FOREIGN KEY constraint
    FOREIGN KEY ([LinkedBarId])
    REFERENCES [dbo].[Bar] ([Id]) 
        ON DELETE CASCADE
        ON UPDATE CASCADE
GO

The following query provides a view of all constraints in a database.

SELECT
 db_name() AS DbName,
 sys_table.name as TableName,
 user_name(sys_column.uid) as SchemaName,
 sys_column.name as ConstraintName,
 col.name as ColumnName,
 col.colid as OrdinalPosition,
 comments.text as DefaultClause 
  FROM sysobjects sys_column
  JOIN syscomments comments ON sys_column.id = comments.id
  JOIN sysobjects sys_table ON sys_column.parent_obj = sys_table.id 
  JOIN sysconstraints con ON sys_column.id = con.constid 
  JOIN syscolumns col ON sys_table.id = col.id AND con.colid = col.colid
 WHERE sys_column.uid = user_id() AND sys_column.xtype = 'D'

GO

See more at W3resource | Wiki.