CODEWITHSUNDEEP
sqlsql-server
Novato1603
Novato1603

Reputation: 3

Unique key do not repeat themselves

I have a table with these columns:

Id -
Part number A - 
Part number B -

I need you to not allow me the following

Id | PartNumberA | PartNumberB  
---+-------------+------------
 1 | TEST        | TEST            -> not allowed, because PartNumberA is equal to PartNumberB
 2 | TEST        | BLAH            -> not allowed
 3 | FFF         | BLAH            -> not allowed

I try to create a unique key:

CREATE UNIQUE NONCLUSTERED INDEX [IX_Producto_PartNumberA_PartNumberB] 
ON [dbo].[Producto] ([PartNumberA] ASC, [PartNumberB] ASC)

But if I try to insert the value "TEST" in both columns, it allows it and I don't want that.

I need the columns not to repeat themselves, and between each other

Upvotes: 0

Views: 91

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1269513

You can't really have a unique constraint that spans both columns. However, you can come pretty close with the following contraints:

  • PartNumberA is unique
  • PartNumberB is unique
  • PartNumberA < PartNumberB

That would be:

create unique index unq_producto_partnumberA on producto(partnumberA);

create unique index unq_producto_partnumberB on producto(partnumberB);

alter table producto add constraint chk_producto_AB check (partnumberA < partnumberB);

This might not meet exactly your needs -- but if you can insist on an ordering for the columns, then it would seem to do what you want.

Upvotes: 0

Arun Palanisamy
Arun Palanisamy

Reputation: 5459

If I understood you correctly, you want UNIQUE & CHECK constraints

CREATE TABLE test (
    ID int,
    PartNumberA varchar(255) UNIQUE,
    PartNumberB varchar(255) UNIQUE,
    CONSTRAINT chk_test CHECK (PartNumberA<>PartNumberB)
);

Upvotes: 1

Related Questions