+- 0Day Forums (https://0day.red)
+-- Forum: Coding (https://0day.red/Forum-Coding)
+--- Forum: Database (https://0day.red/Forum-Database)
+---- Forum: Microsoft SQL Server (https://0day.red/Forum-Microsoft-SQL-Server)
+---- Thread: SQL Server 2005 How Create a Unique Constraint? (/Thread-SQL-Server-2005-How-Create-a-Unique-Constraint)
Pages:
1
2
SQL Server 2005 How Create a Unique Constraint? - banksias541125 - 07-31-2023
How do I create a unique constraint on an existing table in SQL Server 2005?
I am looking for both the TSQL and how to do it in the Database Diagram.
RE: SQL Server 2005 How Create a Unique Constraint? - euphemiaigzx - 07-31-2023
ALTER TABLE dbo.<tablename> ADD CONSTRAINT
<namingconventionconstraint> UNIQUE NONCLUSTERED
(
<columnname>
) ON [PRIMARY]
RE: SQL Server 2005 How Create a Unique Constraint? - croc199766 - 07-31-2023
You are looking for something like the following
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
UNIQUE column_b
[MSDN Docs][1]
[1]:
[To see links please register here]
RE: SQL Server 2005 How Create a Unique Constraint? - thriftboxgaeujo - 07-31-2023
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.
RE: SQL Server 2005 How Create a Unique Constraint? - seminarizerxbrg - 07-31-2023
I also found you can do this via, the database diagrams.
By right clicking the table and selecting Indexes/Keys...
Click the 'Add' button, and change the columns to the column(s) you wish make unique.
Change Is Unique to Yes.
Click close and save the diagram, and it will add it to the table.
RE: SQL Server 2005 How Create a Unique Constraint? - vertebre109264 - 07-31-2023
The SQL command is:
ALTER TABLE <tablename> ADD CONSTRAINT
<constraintname> UNIQUE NONCLUSTERED
(
<columnname>
)
See the full syntax [here][1].
If you want to do it from a Database Diagram:
- right-click on the table and select 'Indexes/Keys'
- click the Add button to add a new index
- enter the necessary info in the Properties on the right hand side:
- the columns you want (click the ellipsis button to select)
- set Is Unique to Yes
- give it an appropriate name
[1]:
[To see links please register here]
RE: SQL Server 2005 How Create a Unique Constraint? - liminal500 - 07-31-2023
Warning: Only one null row can be in the column you've set to be unique.
You can do this with a filtered index in SQL 2008:
CREATE UNIQUE NONCLUSTERED INDEX idx_col1
ON dbo.MyTable(col1)
WHERE col1 IS NOT NULL;
See
[To see links please register here]
for a range of answers.RE: SQL Server 2005 How Create a Unique Constraint? - ampere593990 - 07-31-2023
ALTER TABLE [TableName] ADD CONSTRAINT [constraintName] UNIQUE ([columns])
RE: SQL Server 2005 How Create a Unique Constraint? - charlymwvquqqejz - 07-31-2023
In SQL Server Management Studio Express:
- Right-click table, choose **Modify** or **Design(For Later Versions)**
- Right-click field, choose **Indexes/Keys...**
- Click **Add**
- For **Columns**, select the **field name** you want to be unique.
- For **Type**, choose **Unique Key**.
- Click **Close**, **Save** the table.
RE: SQL Server 2005 How Create a Unique Constraint? - aphasia182 - 07-31-2023
To create a UNIQUE constraint on one or multiple columns when the table is already created, use the following SQL:
ALTER TABLE TableName ADd UNIQUE (ColumnName1,ColumnName2, ColumnName3, ...)
To allow naming of a UNIQUE constraint for above query
ALTER TABLE TableName ADD CONSTRAINT un_constaint_name UNIQUE (ColumnName1,ColumnName2, ColumnName3, ...)
*The query supported by MySQL / SQL Server / Oracle / MS Access.*