How to add Foreign Key in SQL Management Studio

I am going to show you , how to add a Foreign Key to a table from an another table in SQL Management Studio.

When you are dealing with the SQL data base with the .Net technologies , it is very easy to handle your SQL data base using the “SQL Management Studio“.

Lets see how can we do this….

Step 1

• Go to the table design of selected DB table.
• then select the attribute which needed to be set as foreign key .

Step 2

• Right click and get the menu, then select the “Relationships” from the menu

• then new window will appear..

Step 3

Click on the [+] button under “Table And Columns Specifications” and then again click on the button which is in right side.

then new window will appear…

Step 4

• In new window fill needed info related to the FK.
• Select Primary key table and Its Primary Key attribute.
• Select the Foreign Key table and Foreign Key attribute.

Step 5

• Click OK button in current window and then click Close button in next window.
• Now you have set Foreign Key to link tow tables.
• You will see created FK in Keys dialog box also..

Cheers..!  :-)

Convert Rows to Column using COALESCE() function

CREATE TABLE [dbo].[tbl_name](

      [name] [varchar](50) NULL,

      [city] [varchar](50) NULL

) ON [PRIMARY]

GO

SET ANSI_PADDING OFF

GO

INSERT [dbo].[tst] ([name], [city]) VALUES (N'm', N'c1')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'm', N'c2')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'm', N'c3')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'n', N'd1')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'n', N'd2')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'p', N'e1')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'p', N'e2')

INSERT [dbo].[tst] ([name], [city]) VALUES (N'p', N'e3')

DECLARE @eMailList nvarchar(max)

SELECT @eMailList = COALESCE(@eMailList + ';', '') +

  CAST(name AS nvarchar(max))

FROM tbl_name

Select @eMailList as eMailList

For Multiple Column:

SELECT name, city = STUFF(( SELECT ', ' + city

                            FROM dbo.tbl_name

                            WHERE name = x.name

                            FOR XML PATH(''), TYPE).value('.[1]','nvarchar(max)'), 1, 2, '')

FROM dbo.tbl_name AS x

GROUP BY name