April 26, 2013 at 12:47 pm
Hi,
I have a table that contains, among other columns, 4 columns that contain the same type of data, call them Chip1, Chip2, Chip3, Chip4. I want to create an Index that will check that the same entry cannot appear more than once in any row of any of the columns.
So, if Chip1 = ABC then ABC can only appear once in Chip1 and not at all in Chip2, 3 or 4. It would be better to do this as a one-to-many to another table but unfortunately the structure exists already. Is there a way of setting up a constraint that will prevent duplicates?
Thanks
April 26, 2013 at 1:13 pm
As you said normalization would be the best approach but you can accomplish with something like this:
create table CPU
(
chip1 int,
chip2 int,
chip3 int,
chip4 int
)
alter table CPU
add constraint CPU_DupeCheck CHECK
(
chip1 <> chip2
and chip1 <> chip3
and chip1 <> chip4
and chip2 <> chip3
and chip2 <> chip4
and chip3 <> chip4
)
--this will work
insert CPU
select 1, 2, 3, 4
--this will fail
insert CPU
select 1, 2, 3, 3
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply