Unique constraint on two columns regardless of order
You could maybe create computed column, something like:
CREATE TABLE [Car]
(
CarID int NOT NULL PRIMARY KEY IDENTITY(1,1),
FirstColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
SecondColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
xColor As Cast(Case When FirstColorID > SecondColorID Then FirstColorID Else SecondColorID End as varChar) + ',' +
Cast(Case When FirstColorID <= SecondColorID Then SecondColorID Else FirstColorID End as varChar),
UNIQUE(xColor)
)
UPDATE (You should test it before, i did just very quick testing)
Idea:
Integer is 4 byts. If i put 2 integers together- i get 8 bytes. If i order them- i get unique BigInt value (8 bytes).
So what i do:
- I make sure they are in correct order
- I shift bytes of 32 bits to left (so just by multiplying 4294967296 i get what i want)
- I make logical OR- so i get 8 byte BigInt value, that should always be unique!
So:
CREATE TABLE [Car]
(
CarID int NOT NULL PRIMARY KEY IDENTITY(1,1),
FirstColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
SecondColorID int, --FOREIGN KEY REFERENCES Colors(ColorID),
xColor As
Case When FirstColorID > SecondColorID Then
Cast(FirstColorID as BigInt) * Cast(4294967296 as BigInt) | Cast(SecondColorID as BigInt)
Else
Cast(SecondColorID as BigInt) * Cast(4294967296 as BigInt) | Cast(FirstColorID as BigInt)
End
UNIQUE(xColor)
)
SQL Server 2005 Unique constraint on two columns
In SQL Server, a unique constraint is really implemented as a unique index. Use:
CREATE UNIQUE INDEX <uix_name> ON <table_name>(<col_A>, <col_B>)
For more info, see this MSDN page.
Unique constraint on two fields, and their opposite
Two solutions, both really about changing the problem into an easier one. I'd usually prefer the T1
solution if forcing a change on consumers is acceptable:
create table dbo.T1 (
Lft int not null,
Rgt int not null,
constraint CK_T1 CHECK (Lft < Rgt),
constraint UQ_T1 UNIQUE (Lft,Rgt)
)
go
create table dbo.T2 (
Lft int not null,
Rgt int not null
)
go
create view dbo.T2_DRI
with schemabinding
as
select
CASE WHEN Lft<Rgt THEN Lft ELSE Rgt END as Lft,
CASE WHEN Lft<Rgt THEN Rgt ELSE Lft END as Rgt
from dbo.T2
go
create unique clustered index IX_T2_DRI on dbo.T2_DRI(Lft,Rgt)
go
In both cases, neither T1
nor T2
can contain duplicate values in the Lft,Rgt
pairs.
Unique constraint on combination of two columns?
You can do this using an index on expressions:
create unique index unq_test_a_b on (test(least(a, b), greatest(a, b));
I don't think the unique
constraint allows expressions (and don't have a convenient Postgres to test on right now), but this is essentially the same thing.
How can I create a SQL unique constraint based on 2 columns?
You can try this:
CREATE UNIQUE CLUSTERED INDEX index_name ON TABLE (col1,col2)
or
CREATE UNIQUE NONCLUSTERED INDEX index_name ON TABLE (col1,col2)
or
ALTER TABLE [dbo].[TABLE] ADD CONSTRAINT
UNIQUE_Table UNIQUE CLUSTERED
(
col1,
col2
) ON [PRIMARY]
Does unique constraint on multiple columns create index on each column
It will create only one index on two columns and both the column combinedly can not contain duplicate in the entire table.
You can add multiple duplicates for colA
but considering that the colB
is different for each row having same colA
and vice-versa.
colA colB
Tejash SO
Tejash SO1
Tejash SO2
Allowed
or
colA colB
SO1 TEJASH
SO2 TEJASH
SO3 TEJASH
is also allowed.
Unique constraint on two columns regardless of order - Oracle
Oracle supports indexes on expressions, so:
create index unq_t_from_to on t(least(from, to), greatest(from, to))
(Of course, from
and to
are keywords in SQL so they are not very good for column names.)
Related Topics
MySQL Correlated Subquery in Join Syntax
How to Get The SQL String from a JPA Query Object
Freetds and Unixodbc Character Converting
Oracle - Clone Table - Structure, Data Constraints and All
How to Retrieve a Column Value by Name Using Golang Database/Sql
Could Not Find Server 'server Name' in Sys.Servers. SQL Server 2014
How to Pass Two SQL Tables as Input Parameter for R Codes in SQL Server
What Is {Ts '2013-04-02 00:00:00'}
Retrieving SQL Queries from Active-Record Queries in Rails 3
Counter_Cache Has_Many_Through SQL Optimisation, Reduce Number of SQL Queries
Best Practice for Naming SQL Table Columns