Constraint for 2 fields to be consistent

Question

Constraint for 2 fields to be consistent

terry999

SSCertifiable

Points: 5036
More actions
April 27, 2010 at 4:46 am

#234299

Would like to enforce unique combinations but not PK/Unique index
E.G. allows
ID, ID2
1, 2
1, 2
2, 1
But this Data would not be allowed as ID=1 should always be mapped to ID2 =2
ID, ID2
1, 2
1, 3
2, 1
2, 2
For a Field ID value it should always have the same value of field ID2 and vice-versa
I cannot change table structure and put the ID2 field in another table
Thanks

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

vidya_pande SSCertifiable Points: 5275 More actions · Answer 1

do not understand how you want to implement unique constraint without using PK/Unique constraint? This is the simplest way than using trigger etc

Lowell SSC Guru Points: 323518 More actions · Answer 2

terry you'll need to explain the "rules" a bit clearer. I'm not sure why a unique index on the two columns does not do what you want.

i'm just guessing, but here's what i think the rules are you are trying to enforce.

in a single Row, ID2 must be greater than ID1. is that right?

when someone tries to insert ID1 greater than ID2 , a mirror of the keys must exist; ie to insert 5,2 then 2,5 must already be in the table.

terryshamir (4/27/2010)
Would like to enforce unique combinations but not PK/Unique index
ID, ID2
1, 2 <--the first record?
1, 2 <--duplicates should be allowed? then it's not unique
2, 1 <--this should be allowed because it is the mirror of 1,2?
But this Data would not be allowed as ID=1 should always be mapped to ID2 =2
ID, ID2
1, 2 <--the first record?
1, 3 <--this is not alowed because 1 is already mapped to something?
2, 1 <--this should be allowed because it is the mirror of 1,2?
2, 2 <--why not this record?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

terry999 SSCertifiable Points: 5036 More actions · Answer 3

Sorry for confusion, hope this explains it better

Have a table with an ID field which is not part of the PK.

ID always contains a value about 97% unique.

I want to make sure that the other field say CustomerName field contains the same value for a particular value in the ID field

E.G.

[font="Courier New"]

ID CustomerName Sold

1 Gary 10

2 Terry 12[/font]

So if It will accept

[font="Courier New"]

2 Terry 14[/font]

but NOT

[font="Courier New"]

2 Bob 16[/font]

Because the ID 2 should always have name=Terry.

REALLY should be in another customer table, but it is as it is.

vidya_pande SSCertifiable Points: 5275 More actions · Answer 4

If you want to achieve this with single table then there is possibility of implementing it using trigger.

Usually such kind of constraints are implemented using FK's and normalizing the table into two tables.

Lowell SSC Guru Points: 323518 More actions · Answer 5

ok that helps explain the logic, thanks.

is there a master table that has value like this?:

1 Gary

2 Terry

3 Bob

so we could lookup/compare the proper name that should be tied to the ID?

if there is not, there'd be no way to KNOW which record is the correct one: 2 Terry or 2 Bob

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

terry999 SSCertifiable Points: 5036 More actions · Answer 6

Thanks guys

There is no external table (tblcustomer), the current data is correct, where the same value appears in field ID more than once the customer name is the same in both records.

I would like to keep it this way.

Could I write a function and put in the contsraint criteria that checks that this customername doesn't exist for the ID value

Function (@ID as int, @Name as varchar)

if exists (select * from tbl where id= @id and cusname<> @Name)

raiseerror 16,1, "Invlaid name for this ID"

return 0 -- invalid

else

return 1 -- valid

But how to put this in definition...