ORA-00902 invalid datatype when trying to create a table with primary key based on two columns in Oracle 10g
I was trying to create a table with primary key based on two columns. Here is the create table statement: CREATE TABLE SECURITY_PASSWORD ( USER_NAME VARCHAR2 (25 BYTE) NOT NULL, USER_PASSWORD VARCHAR2 (25 BYTE) NOT NULL, CREATED_DATE DATE NOT NULL, CONSTRAINT security_password_pk PRIMARY_KEY (user_name,user_password) ); Unfortunately I keep getting error message: ORA-00902 invalid datatype.This is on Oracle 10g version 10.2.o.4 Need help. Thank you
Software/Hardware used:
Oracle 10g
Software/Hardware used:
Oracle 10g
ASKED:
June 25, 2010 5:12 PM
UPDATED:
June 26, 2010 3:28 AM
Answer Wiki:
Change the constraint declaration. It should be:
<pre>CONSTRAINT security_password_pk PRIMARY KEY (user_name,user_password)</pre>
(No underscore between PRIMARY and KEY)
And, as Mrdenny suggests, don't inlcude the password in the PK (if you do it won't cause errors when creating the table, but it would nullify the purpose of the pk).
To see all answers submitted to the Answer Wiki: View Answer History.
You should change the primary key to being just the username field. With the current primary key you can have two people with the same username, as long as they have different passwords. Putting the primary key on the username will allow you to have unique usernames.
The idea of making username and password a primary key is to prevent one user from entering the same password as any previously used 24 passwords during password change. In addition there is no any established rule to enforce username to be unique. In a table where username and password are stored, there are already duplicate usernames.
How this can be corrected?
Carlosdl: Can you explain how the purpose of primary key can be nullified if you use a cobination of two field as primary key?
Thanks
I didn’t say that the purpose of the pk would be nullified if you use a cobination of two fields as primary key. I said it would nullify it in this specific case (for the reasons exposed by Mrdenny).
We were assuming about the usage of this table since no information was provided in that respect. The user should be unique if this is the main user catalog, but I guess it is not. It seems that you will be using this table to store password change events only.
If you create the primary key including the password, it won’t allow the same user-password combination never (if you want to prohibit the use of the last 24 different passwords only, this is not the correct approach).
On the other hand, if passwords are being stored encrypted, then, depending on the encryption method, the same password could result in a different string each time it is encrypted, and the pk would not prevent it from being stored.
I apologize for not explaining the useage of the table.
The table is used to store unique password and username combinations that were used in the past two years for each application user. So the actual senario is that one particular application user’s password expires every 90 days and he/she must change the password. The previousely used username and password combination must be stored in a table. Next time (after 90 days) when this same user changes his/her password the system has to check that the new password for this particular user must not be any of the passwords that have been used in the last two years. So, my question is this. How I can accomplish this requirement by making only username a primary key?
Thanks a lot