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.
Posts
