Boathill

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

Big Four Ice Mountain

leave a comment »


行香子·夏入雪峰

高嶺重巒,暑氣消殘。
 水窮處,野趣營盤。
松林花徑,光影斑斕。
看冬凝玉,
 春流澗,
 夏成潭。
  

地聚峰連,古化熔岩。
 千年雪,陸海奇緣。
山陰背後,滴水洞前。
數四冰窟,
 兩坡綠,
 一天藍。

2014.07.12. Granite Falls

♥ 參見:行香子詞譜

Written by Boathill

2014-07-12 at 19:00

July 4 in Oregon

leave a comment »

Written by Boathill

2014-07-06 at 22:00

Posted in Photograghy

Tagged with

PhotoShop Alternatives

leave a comment »


See a more complete list at DigiCamHelp and detailed review here.

Other photo edit/view tools:

Written by Boathill

2014-07-05 at 21:00

Posted in IT, Photograghy, review

Tagged with , ,

Sammamish

leave a comment »

Written by Boathill

2014-06-24 at 22:00

Posted in Photograghy

Tagged with ,

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 , , , ,

I-90 East Washington

leave a comment »

Written by Boathill

2014-05-05 at 22:00

Posted in Photograghy

Renton, WA

leave a comment »

Written by Boathill

2014-04-26 at 21:00

Posted in Photograghy

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: