<p>How do I create a unique constraint on an existing table in SQL Server 2005?</p>

<p>I am looking for both the TSQL and how to do it in the Database Diagram.</p>

[{'answer_id': 65003, 'author': 'Ivan Bosnic', 'author_id': 3221, 'author_profile': 'https://Stackoverflow.com/users/3221', 'pm_score': 4, 'selected': False, 'text': '<pre><code>ALTER TABLE dbo.&lt;tablename&gt; ADD CONSTRAINT\n            &lt;namingconventionconstraint&gt; UNIQUE NONCLUSTERED\n    (\n                &lt;columnname&gt;\n    ) ON [PRIMARY]\n</code></pre>\n'}, {'answer_id': 65022, 'author': 'Thunder3', 'author_id': 2832, 'author_profile': 'https://Stackoverflow.com/users/2832', 'pm_score': 3, 'selected': False, 'text': '<p>You are looking for something like the following</p>\n\n<pre><code>ALTER TABLE dbo.doc_exz\nADD CONSTRAINT col_b_def\nUNIQUE column_b\n</code></pre>\n\n<p><a href="http://msdn.microsoft.com/en-us/library/ms190273.aspx" rel="noreferrer">MSDN Docs</a></p>\n'}, {'answer_id': 65025, 'author': 'Gibbons', 'author_id': 1506, 'author_profile': 'https://Stackoverflow.com/users/1506', 'pm_score': 3, 'selected': False, 'text': '<p>In the management studio diagram choose the table,  right click to add new column if desired, right-click on the column and choose "Check Constraints", there you can add one.</p>\n'}, {'answer_id': 65036, 'author': 'David Basarab', 'author_id': 2469, 'author_profile': 'https://Stackoverflow.com/users/2469', 'pm_score': 3, 'selected': False, 'text': "<p>I also found you can do this via, the database diagrams.</p>\n\n<p>By right clicking the table and selecting Indexes/Keys...</p>\n\n<p>Click the 'Add' button, and change the columns to the column(s) you wish make unique.</p>\n\n<p>Change Is Unique to Yes.</p>\n\n<p>Click close and save the diagram, and it will add it to the table.</p>\n"}, {'answer_id': 65047, 'author': 'WildJoe', 'author_id': 9052, 'author_profile': 'https://Stackoverflow.com/users/9052', 'pm_score': 5, 'selected': False, 'text': '<pre><code>ALTER TABLE [TableName] ADD CONSTRAINT  [constraintName] UNIQUE ([columns])\n</code></pre>\n'}, {'answer_id': 65123, 'author': 'Rory', 'author_id': 8479, 'author_profile': 'https://Stackoverflow.com/users/8479', 'pm_score': 9, 'selected': True, 'text': '<p>The SQL command is:</p>\n\n<pre><code>ALTER TABLE &lt;tablename&gt; ADD CONSTRAINT\n            &lt;constraintname&gt; UNIQUE NONCLUSTERED\n    (\n                &lt;columnname&gt;\n    )\n</code></pre>\n\n<p>See the full syntax <a href="http://msdn.microsoft.com/en-us/library/ms190273.aspx" rel="noreferrer">here</a>.</p>\n\n<p>If you want to do it from a Database Diagram:</p>\n\n<ul>\n<li>right-click on the table and select \'Indexes/Keys\'</li>\n<li>click the Add button to add a new index</li>\n<li>enter the necessary info in the Properties on the right hand side:\n\n<ul>\n<li>the columns you want (click the ellipsis button to select)</li>\n<li>set Is Unique to Yes</li>\n<li>give it an appropriate name</li>\n</ul></li>\n</ul>\n'}, {'answer_id': 537780, 'author': 'Squirrel', 'author_id': 11835, 'author_profile': 'https://Stackoverflow.com/users/11835', 'pm_score': 4, 'selected': False, 'text': '<p>Warning: Only one null row can be in the column you\'ve set to be unique.</p>\n\n<p>You can do this with a filtered index in SQL 2008:</p>\n\n<pre><code>CREATE UNIQUE NONCLUSTERED INDEX idx_col1\nON dbo.MyTable(col1)\nWHERE col1 IS NOT NULL;\n</code></pre>\n\n<p>See <a href="https://stackoverflow.com/questions/377798/field-value-must-be-unique-unless-it-is-null">Field value must be unique unless it is NULL</a> for a range of answers.</p>\n'}, {'answer_id': 1313576, 'author': 'James Lawruk', 'author_id': 88204, 'author_profile': 'https://Stackoverflow.com/users/88204', 'pm_score': 6, 'selected': False, 'text': '<p>In SQL Server Management Studio Express:</p>\n\n<ul>\n<li>Right-click table, choose <strong>Modify</strong> or <strong>Design(For Later Versions)</strong></li>\n<li>Right-click field, choose <strong>Indexes/Keys...</strong></li>\n<li>Click <strong>Add</strong></li>\n<li>For <strong>Columns</strong>, select the <strong>field name</strong> you want to be unique.</li>\n<li>For <strong>Type</strong>, choose <strong>Unique Key</strong>.</li>\n<li>Click <strong>Close</strong>, <strong>Save</strong> the table.</li>\n</ul>\n'}, {'answer_id': 27223345, 'author': 'Rafiq', 'author_id': 847501, 'author_profile': 'https://Stackoverflow.com/users/847501', 'pm_score': 3, 'selected': False, 'text': '<p>To create a UNIQUE constraint on one or multiple columns when the table is already created, use the following SQL:</p>\n\n<pre><code>ALTER TABLE TableName ADd UNIQUE (ColumnName1,ColumnName2, ColumnName3, ...)\n</code></pre>\n\n<p>To allow naming of a UNIQUE constraint for above query</p>\n\n<pre><code>ALTER TABLE TableName ADD CONSTRAINT un_constaint_name UNIQUE (ColumnName1,ColumnName2, ColumnName3, ...)\n</code></pre>\n\n<p><em>The query supported by MySQL / SQL Server / Oracle / MS Access.</em></p>\n'}, {'answer_id': 49405468, 'author': 'Mario Vázquez', 'author_id': 8928707, 'author_profile': 'https://Stackoverflow.com/users/8928707', 'pm_score': 0, 'selected': False, 'text': "<p>In some situations, it could be desirable to ensure the Unique key does not exists before create it. In such cases, the script below might help:</p>\n\n<pre><code>IF Exists(SELECT * FROM sys.indexes WHERE name Like '&lt;index_name&gt;')\n    ALTER TABLE dbo.&lt;target_table_name&gt; DROP CONSTRAINT &lt;index_name&gt; \nGO\n\nALTER TABLE dbo.&lt;target_table_name&gt; ADD CONSTRAINT &lt;index_name&gt; UNIQUE NONCLUSTERED (&lt;col_1&gt;, &lt;col_2&gt;, ..., &lt;col_n&gt;) \nGO\n</code></pre>\n"}]
['https://Stackoverflow.com/questions/64981', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/2469/']