August 1, 2007 at 6:55 am
From a table or database, how can i find which column has unique values in it. In most of the tables I have IDENTITY value as PK, and by finding the columns which has unique values, I wud like to have a unique contraint on it. These are very old DB's which i did not designed.
August 1, 2007 at 6:59 am
SELECT CASE WHEN COUNT(DISTINCT Col1) = COUNT(Col1) THEN 'All unique' ELSE 'Some duplicates' END
FROM Table1
N 56°04'39.16"
E 12°55'05.25"
August 1, 2007 at 7:13 am
do i need to check through each column? I wud like to check complete table if it has any coulumns that has unique data in it.
above query has syntax error.
August 1, 2007 at 7:24 am
Start with the code above and go from there and use DYNAMIC SQL to automate this process.
N 56°04'39.16"
E 12°55'05.25"
August 1, 2007 at 7:51 am
If I have columns userid & password, can I have unique contraint on both of them so that both userid and password should be unique giving a chance for either userid or password to be duplicate
August 1, 2007 at 8:10 am
If I understand you correctly you want (USERID+PWD) to be unique instead of USERID being unique by itself. Is that right? Or did you intend that NEITHER USERID NOR PWD should be repeated?
The latter would be recommended but if you want the former, then it too is possible although I would not know the precise SQL syntax to achieve it (some ALTER COLUMN thing). However, I would like you to consider the consequences of such a constraint:
You may end up having the following values:
USERID....PWD
JOHN......DEER
JOHN......DOE
JAMES.....DEER
JAMES.....DOE
JAMES.....ONLYME
and then you would get a failure when you try to insert
JAMESON...LYME
Hope I've made sense.
August 1, 2007 at 8:15 am
I am not sure about unique contraints..does it work in that way. Actually I thought I can have a unique constraint with userid and pswd together. here userid can repeat and pswd can also repeat but both together can not repeat.
I hope the composite key works in that way then why not a unique constraint.
August 1, 2007 at 8:35 am
A UNIQUE constraint is theoretically no different from a PRIMARY KEY, since a KEY has to be UNIQUE in order to retrieve records, and a UNIQUE column will do just the same function, except that a PRIMARY KEY builds a clustered INDEX.
Or are you looking to build a COMPOSITE INDEX instead?
As I showed in the scenario above what may happen is that you risk succeeding in INSERTing multiple rows for a single USERID and failing to INSERT a row for a different user (JAMES+ONLYME vs JAMESON+LYME). Both risks are serious.
August 1, 2007 at 11:47 am
yeah but still i want an unique constraint or what ever for both userid and passowrd together.
Can i have constraint on existing table though it has duplicates existed, i want to enforce constraint from now on.
August 1, 2007 at 7:47 pm
Just an FYI...Don't forget the other difference... UNIQUE key allows 1 NULL as a value... Primary Key won't.
And, you can also build a PK without it being clustered, although there a few cases where you should do that.
--Jeff Moden
Change is inevitable... Change for the better is not.
August 2, 2007 at 12:45 am
Jeff: Thanks for illuminating.
JP: Again, I need to ask you: WHY on earth would you want UNIQUE passwords? How would you deal with this situation: if by a fluke of nature two users happen to settle on the same password (maybe both fancy the same girl) and the second one is denied registration because "PASSWORD ALREADY EXISTS".
Having let the cat out of the bag, you will only have yourself to blame if an enterprising crooked mind sits down and traces the USERID that owns that password.
One of the best login security I have seen so far is that of the old Novell Netware: Whether you get the UserID right or wrong, as long as the password is wrong, you get only one message:
"Password incorrect." That way a hacker would never be able to know whether the USERID actually exists!
Novell was also one of the earliest to implement forced password changes, minimum password lengths and, something I have yet to see elsewhere, it stored your last nine password changes to prevent you from recycling from a small pool of passwords. And it wouldn't allow you to use your Login name or "PASSWORD" as a password.
Sorry, I've gone of off on a tangent, but when I see such attempts as what you are trying to acomplish, I just feel like writing a book on user security and user rights. Maybe one day I will.
I hope I've made sense. Do not hesitate to ask more questions.
August 2, 2007 at 6:40 am
Hey good guy..I accept it but i am not just dealing with password unique though they have same password they may have different userid, what i need is there can not be same userid andpassword dupicated..for example
Uerid password
John dba -(allowed)
John qat -(allowed)
Tom dba -(allowed)
Tom qat -(allowed)
John dba ( dont allow because already exists in the 1st case)
August 2, 2007 at 8:25 am
Starts making sense, but only so much, because it is still poor logic (and bad ethics).
Next question: why does one user need multiple passwords? Why can't you implement user rights to get the same effect? It is neater, while the former is messier in that a user has to remember multiple passwords (I am assuming each pwd is for different modules).
More so, who sets the passwords? And how does the system distinguish which password value has what rights? If, as I suspect, you have a few fixed passwords, then where does the accountability come in if John can easily log in with Tom's userid and commit fraud?
Hope you can see where I am headed.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply