SQL question

[CTho9305]

Can I create a type of unique index that treats 2 columns as one? I have a username column and a displayname column. They are not necessarily equal, and when you insert a new row or update one, I want to make sure that the displayname doesn't match any other displayname AND doesn't match any usernames. Same for username. A multiple-column unique index doesn't do what I want.

This just strikes me as a kinda braindead way to do it (if there are results, you have a duplicate): SELECT ID FROM t_user WHERE displayname=$username or displayname=$displayname or username=$username or displayname=$username)

 

[EagleKeeper]

Create a thrid field that is a combination of what you are trying to do. Force that field to be unique. Test against that column instead.

 

[CTho9305]

Originally posted by: EagleKeeper
Create a thrid field that is a combination of what you are trying to do. Force that field to be unique. Test against that column instead.

Can you give me some sql? I dont think that would be doable. Look at it like this... some forum software lets you have your displayed username (one column) different from your actual login (another column). When a new user joins, I want to make sure they aren't using the same login OR display name as anyone else already. I don't see how combining the oclumns into a 3rd would help. what I need is the equivalent of a single column that contains the values of both and is twice as long. having a table of just username/displaynames with one column only seems even worse than the SELECT I posted above...

 

[manly]

Use a trigger, which is a feature of your specific SQL dialect. Unless you have a toy DBMS.

 

[Lint21]

That's probably what I would do. Create a trigger that checks the value that was inserted against both the username and loginname columns. You could do this through the third column suggested above (the trigger would insert new values from the two columns into the third), or you might be able to make a trigger that checks against the existing columns. Maybe something like this?:


declare @displayname varchar 255
declare @username varchar 255
declare @check int

--Get our variables from the "imaginary" Inserted table.
set @displayname = select distinct displayname from inserted
set @username = select distinct username from inserted
--Changing value, increments by one every time there is a match
set @check = 0


--Begin checks
if exists

select *
from t_user t (nolock)
where t.displayname = @displayname

begin
set @check = @check + 1
end

if exists

select *
from t_user t (nolock)
where t.displayname = @username

begin
set @check = @check + 1
end

if exists

select *
from t_user t (nolock)
where t.username = @displayname

begin
set @check = @check + 1
end

if exists

select *
from t_user t (nolock)
where t.username = @username

begin
set @check = @check + 1
end

--CHeck @check, rollback if name in use
if @check > 0
begin
raiserror("This username is already in use as a username or loginname", 16, 1)
rollback transaction
return
end


That code sucks and is probably going to give a DBA or two a conniption fit (I'm tired as hell), but it might give you an idea, or at least get you thinking of another possibility. Oh, and it's in T-SQL format, I'm not sure what "flavor" you use.

As everyone know, triggers can really destroy a DB's performance is used without caution, so be careful. I don't know of a way to create an index or constraint to do what you want to do... it sounds tough, good luck

 

[CTho9305]

mysql is lame
i wonder if the oracle license lets me use it for free... does screwing around with SQL count as development?