Oracle unique user id and username

quinnd6

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?

Evil Phil

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

quinnd6

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

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)

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.

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.