Menu
Introduction to SQLite UNIQUE constraint To define a UNIQUE constraint, you use the UNIQUE keyword followed by one or more columns. You can define a UNIQUE constraint at the column or the table level. Only at the table level, you can define a UNIQUE constraint across multiple columns.
SQL UNIQUE constraint for 2 columns example Notice that we named the UNIQUE constraints using CONSTRAINT keyword. We can use this name to remove the UNIQUE constraint later if we want. To define a UNIQUE on multiple columns, we put a comma-separated columns list inside parenthesis that follows the UNIQUE keyword.
The syntax for creating a unique constraint using an ALTER TABLE statement in MySQL is: ALTER TABLE table_name ADD CONSTRAINT constraint_name UNIQUE (column1, column2, ... column_n); table_name.
Defining Composite Unique Keys Oracle creates an index on the columns of a unique key, so a composite unique key can contain a maximum of 16 columns.
If the table is already created in the database, then you can add a unique constraint later on by using this SQL query:
ALTER TABLE dbo.User
ADD CONSTRAINT ucCodes UNIQUE (fcode, scode, dcode)
By using the constraint definition on table creation, you can specify one or multiple constraints that span multiple columns. The syntax, simplified from technet's documentation, is in the form of:
CONSTRAINT constraint_name UNIQUE [ CLUSTERED | NONCLUSTERED ]
(
column [ ASC | DESC ] [ ,...n ]
)
Therefore, the resuting table definition would be:
CREATE TABLE [dbo].[user](
[userID] [int] IDENTITY(1,1) NOT NULL,
[fcode] [int] NULL,
[scode] [int] NULL,
[dcode] [int] NULL,
[name] [nvarchar](50) NULL,
[address] [nvarchar](50) NULL,
CONSTRAINT [PK_user_1] PRIMARY KEY CLUSTERED
(
[userID] ASC
),
CONSTRAINT [UQ_codes] UNIQUE NONCLUSTERED
(
[fcode], [scode], [dcode]
)
) ON [PRIMARY]
This can also be done in the GUI. Here's an example adding a multi-column unique constraint to an existing table.
Click OK in each window and you're done.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With