antonms
asked on
Table Contraints
Hi
Is it possible to apply a constraint or equivalent to a table that prevents a record being inserted when the record to be inserted already exists but the values are swapped around?
For example, if a table had two columns: FirstName and SecondName having a record FirstName = ‘John’ and SecondName = ‘Smith’ – then when another record having FirstName =’ Smith’ and SecondName =’ John’ was about to be inserted SQL would reject it.
Good luck!
Is it possible to apply a constraint or equivalent to a table that prevents a record being inserted when the record to be inserted already exists but the values are swapped around?
For example, if a table had two columns: FirstName and SecondName having a record FirstName = ‘John’ and SecondName = ‘Smith’ – then when another record having FirstName =’ Smith’ and SecondName =’ John’ was about to be inserted SQL would reject it.
Good luck!
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Scott Pletcher
If you want to avoid a trigger, you could also create a PERSISTED column with a unique index on it. I can give you details on that if you'd like them.
ASKER
If possible I would prefer not to use a trigger - more info on the persisted column option would be great thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks