Unique Key with two interchangeable columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

Database Design and Application Architecture

Unique Key with two interchangeable columns

Author

Topic

m_rajeswaran
Starting Member

2 Posts

Posted - 2011-11-16 : 05:58:37

I have a situation, where I need to create a unique key for two columns whose values are interchangeable. For example, there is a table of line segments. It needs to have columns for "start point" and "end point". The start and end points are interchangeable. It means, uniqueness needs to take care of the interchangeability. If we take end point and start point of an existing row and try to create new row with the points interchanged as start and end points respectively, it should not be allowed.

AndrewMurphy
Master Smack Fu Yak Hacker

2916 Posts

Posted - 2011-11-16 : 09:11:44

Look to implement a (trigger) "instead-of" condition that the lower of the 2 values is always stored in the first column. Regardless of what is input in the insert statement, the data gets stored in the manner most useful to you.

insert into tablea (1,2) result stored 1,2
insert into tablea (2,1) result stored 2,1 (which in this instance will fail because of the previous insert)

m_rajeswaran
Starting Member

2 Posts

Posted - 2011-11-17 : 06:09:54

Thank you.

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 13:13:46

Can't you crerate a rule?

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 13:22:55

OK that doesn't work


CREATE TABLE myTable99(
	  Col1 char(1)
	, Col2 char(1)
	, PRIMARY KEY (Col1, Col2)
	, CHECK (Col1 + Col2 <> (SELECT Col2 + Col1 FROM myTable99 WHERE Col1 = Col2 AND Col2 = Col1))
)
GO

CREATE INDEX myIndex99 ON myTable99(Col2, Col1)

DROP TABLE myTable99
GO

I'd love to underrsatand this from a Data Normalization reason

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

X002548
Not Just a Number

15586 Posts

Posted - 2011-11-17 : 13:32:20

How about this


CREATE TABLE myTable99(
	  Col1 char(1)
	, Col2 char(1)
	, PRIMARY KEY (Col1, Col2)
)
GO

CREATE INDEX myIndex99 ON myTable99(Col2, Col1)
GO

CREATE  TRIGGER  myTrigger99 ON myTable99 FOR INSERT
  AS
	SET NOCOUNT ON
	IF EXISTS (SELECT * FROM inserted i INNER JOIN myTable99 m ON i.Col1 = m.Col2 AND i.Col2 = m.Col1)
		RAISERROR 500001 'End Point Already Exists'
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 'a', 'b'
GO

INSERT INTO myTable99(Col1, Col2)
SELECT 'b', 'a'
GO


DROP TABLE myTable99
GO

Brett

8-)

Hint: Want your questions answered fast? Follow the direction in this link
http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx

Want to help yourself?

http://msdn.microsoft.com/en-us/library/ms130214.aspx

http://weblogs.sqlteam.com/brettk/

http://brettkaiser.blogspot.com/

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2011-11-24 : 21:11:28

A bit late, but here's my 2c:
I dislike triggers because they are "hidden" and cause non-obvious side-effects. I'm not sure if the concurrency of SQL Server would even work with a trigger. For example, if 2 users both check at the same time then add at the same time I think there is a window for getting your duplicate in. It's a bit like the old select max(id)+1 from t for a new ID. It looks like it will work but actually doesn't if you start to stress it.

Personally I'd create a persisted, calculated column of the two values in a consistent order (smallest/largest say) then create a unique index on it.
Yeah I know there's more storage, but you're using the DB engine directly to enforce your rule and all the good things that come with that (like it works).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources