Advertisement
Help Keep Boards Alive. Support us by going ad free today. See here: https://subscriptions.boards.ie/.
If we do not hit our goal we will be forced to close the site.

Current status: https://keepboardsalive.com/

Annual subs are best for most impact. If you are still undecided on going Ad Free - you can also donate using the Paypal Donate option. All contribution helps. Thank you.
https://www.boards.ie/group/1878-subscribers-forum

Private Group for paid up members of Boards.ie. Join the club.

Oracle unique user id and username

  • 05-10-2007 12:13PM
    #1
    quinnd6
    Registered Users, Registered Users 2 Posts: 1,559 ✭✭✭


    I have a bit of a problem.

    I each user in my table to have a unique username and user_id.
    So the table cant contain 2 users with the same user id and it cant contain 2 users with the same username.

    I don't think oracle supports more than one primary key in a table so what could I use a secondary key?


Comments

  • #2
    Evil Phil
    Registered Users, Registered Users 2 Posts: 7,468 ✭✭✭Evil Phil


    I don't know oracle but is there 'Is Unique' constraint you can set on the field?


  • #3
    quinnd6
    Registered Users, Registered Users 2 Posts: 1,559 ✭✭✭quinnd6


    Anyone know about creating alternate keys in sql plus?
    Is that what Im looking for?


  • #4
    Tom Dunne
    Registered Users, Registered Users 2 Posts: 23,202 ✭✭✭✭Tom Dunne


    quinnd6 wrote:
    Anyone know about creating alternate keys in sql plus?
    Is that what Im looking for?

    Are you looking for a composite primary key?

    edit: no you are not

    My initial suggestion would be to have two seperate tables, one for usernames, one for user_ids with a foreign key linking them up.
    I don't know oracle but is there 'Is Unique' constraint you can set on the field?

    There is. ALTER TABLE X ADD UNIQUE(COLUMN_NAME)


  • #5
    quinnd6
    Registered Users, Registered Users 2 Posts: 1,559 ✭✭✭quinnd6


    I read on some webpage that when you use unique it allows you to have blanks.
    So I could end up allowing blank usernames.

    Are you sure its not secondary key Im looking for?

    The user_id could be the primary key and the username could be the secondary key(I think they're called alternate keys in sql plus).


    If only I knew how to create a secondary key in sql plus.
    I cant find how to create a secondary key in sql plus anywhere.


  • #6
    bonkey
    Registered Users, Registered Users 2 Posts: 15,443 ✭✭✭✭bonkey


    Ht concept of "secondary key" doesn't exist in Oracle.

    If its in MySQL, I'd say its probably unique to that system.

    In Oracle, as well as any other ANSI-compliant RDBMS, you have UNIQUE constraints, which are typically enforced using a UNIQUE Index.

    A UNIQUE field may allow a null value, but not if you define the field to be NOT NULL, obviously.


  • Advertisement
Advertisement