boat+hill

·舟山詩詞·淘海洗玉集 – My Poems, and etc.

SQL Named Constraint

leave a comment »

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.

Written by Boathill

2014-05-12 at 20:00

Posted in IT, SQL

Tagged with , , , ,

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: