HELP me on the ALTER TABLE statement, how do I add a UNIQUE Constraint to a table.

Question

HELP me on the ALTER TABLE statement, how do I add a UNIQUE Constraint to a table.

mw_sql_developer

SSCoach

Points: 19445
More actions
June 8, 2018 at 9:25 am

#350553

Good Day!
I need the syntax for the ALTER TABLE table1 .... ADD CONSTRAINT ... [IX_Table_1] ...? HELP
USE [TSQL2012] GO
/****** Object: Table [dbo].[Table_1] Script Date: 11/22/2015 12:45:47 PM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[Table_1]( [seq] [bigint] IDENTITY(1,1) NOT NULL, [ID] [int] NOT NULL, [name] [nvarchar](50) NULL, [cat] [nvarchar](50) NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Table_1] UNIQUE NONCLUSTERED ( [name] ASC, [cat] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO

Viewing 4 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Luis Cazares SSC Guru Points: 183706 More actions · Answer 1

https://docs.microsoft.com/en-us/sql/t-sql/statements/alter-table-transact-sql?view=sql-server-2017#syntax

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

Michael L John One Orange Chip Points: 27079 More actions · Answer 2

Maybe this?
https://docs.microsoft.com/en-us/sql/t-sql/statements/create-index-transact-sql?view=sql-server-2016
Or this?
https://docs.microsoft.com/en-us/sql/relational-databases/tables/create-unique-constraints?view=sql-server-2016
Or this?
https://blogs.msdn.microsoft.com/kvivek/2012/03/10/download-books-online-product-documentation-for-sql-server-2012/

Michael L John
If you assassinate a DBA, would you pull a trigger?
To properly post on a forum:
http://www.sqlservercentral.com/articles/61537/

mw_sql_developer SSCoach Points: 19445 More actions · Answer 3

mw_sql_developer - Friday, June 8, 2018 9:25 AM
Good Day!
I need the syntax for the ALTER TABLE table1 .... ADD CONSTRAINT ... [IX_Table_1] ...? HELP
USE [TSQL2012] GO
/****** Object: Table [dbo].[Table_1] Script Date: 11/22/2015 12:45:47 PM ******/ SET ANSI_NULLS ON GO
SET QUOTED_IDENTIFIER ON GO
CREATE TABLE [dbo].[Table_1]( [seq] [bigint] IDENTITY(1,1) NOT NULL, [ID] [int] NOT NULL, [name] [nvarchar](50) NULL, [cat] [nvarchar](50) NULL, CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED ( [ID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY], CONSTRAINT [IX_Table_1] UNIQUE NONCLUSTERED ( [name] ASC, [cat] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY]
GO

Found the answer! Cool!

ALTER TABLE [dbo].[Table_1] ADD CONSTRAINT [IX_Table_1] UNIQUE NONCLUSTERED ( [name] , [cat] )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO