ORA-00902 invalid datatype when trying to create a table with primary key based on two columns in Oracle 10g

90 pts.
CREATE TABLE statement
Database issues
Oracle 10g error messages
Oracle Table
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

Answer Wiki

Thanks. We'll let you know when a new response is added.

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).

Discuss This Question: 4  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    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.
    68,340 pointsBadges:
  • Techguys
    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
    90 pointsBadges:
  • carlosdl
    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.
    84,495 pointsBadges:
  • Techguys
    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
    90 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: