Home » SQL & PL/SQL » SQL & PL/SQL » Unique/check constraint on two columns (Oracle Database 11g Express Edition Release 11.2.0.2.0)
Unique/check constraint on two columns [message #608411] Wed, 19 February 2014 08:34 Go to next message
kojot
Messages: 27
Registered: December 2009
Location: Poland
Junior Member
Hi!

I want to let my users to login using address e-mail or login. I need to be sure that the user login is diffrent than the other users e-mail, and the e-mail is diffrent than the other user login.

Is it possible to do it with tables constraint?

Example:
CREATE TABLE test_unique (login VARCHAR2(255) NOT NULL UNIQUE, email VARCHAR2(255) UNIQUE);

INSERT INTO test_unique (login, email)
VALUES ('test_login', 'test_email');

--this insert should not be allowed, login is the same like the email in first insert
INSERT INTO test_unique (login, email)
VALUES ('test_email', 'test_email_2');

--this insert should no be allowed too, email is the same like the login in first insert
INSERT INTO test_unique (login, email)
VALUES ('test_login_2', 'test_login');


Regards,
kojot
Re: Unique/check constraint on two columns [message #608414 is a reply to message #608411] Wed, 19 February 2014 08:44 Go to previous messageGo to next message
manubatham20
Messages: 443
Registered: September 2010
Location: Champaign, IL
Senior Member

I will suggest to place a check using fronted server, rather thancoming to database.
Re: Unique/check constraint on two columns [message #608416 is a reply to message #608414] Wed, 19 February 2014 08:48 Go to previous messageGo to next message
BlackSwan
Messages: 22504
Registered: January 2009
Senior Member
>Is it possible to do it with tables constraint?
yes, using UNIQUE constraint

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/
Re: Unique/check constraint on two columns [message #608429 is a reply to message #608416] Wed, 19 February 2014 10:14 Go to previous messageGo to next message
kojot
Messages: 27
Registered: December 2009
Location: Poland
Junior Member
Thank you for reply.

manubatham20, I will use pl/sql procedure for inserts and updates, but for security reasons I really like to have an unique constraint like this.

BlackSwan, could you tell me how to add this unique constraint?

Unique constraint based on one column checks that the values in the column is unique.
Unique constraint based on two columns like:
ALTER TABLE TEST_UNIQUE
ADD CONSTRAINT TEST_UNIQUE_UK1 UNIQUE 
(
  LOGIN 
, EMAIL 
)

checks that pair of values (e-mail and login) is unique in the table. What I want is an unique constraint that checks the value doesn't exist in other column, as I explained in first post.

Could you tell me what is wrong with my posts that you lead me to the forum guidelines? I will correct them.

Regards,
kojot
Re: Unique/check constraint on two columns [message #608437 is a reply to message #608429] Wed, 19 February 2014 12:22 Go to previous messageGo to next message
BlackSwan
Messages: 22504
Registered: January 2009
Senior Member
>What I want is an unique constraint that checks the value doesn't exist in other column, as I explained in first post.
Have two UNIQUE constraints; one on each column individually.
Re: Unique/check constraint on two columns [message #608446 is a reply to message #608437] Wed, 19 February 2014 13:27 Go to previous messageGo to next message
Littlefoot
Messages: 19314
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Why do you have two columns? As you want to allow both "types" of login information (an "ordinary" username as well as user's e-mail address), and they act the same way - let the user log in, why wouldn't you have only one column? So, my login could be Littlefoot or littlefoot@mail.com, but not both.
Re: Unique/check constraint on two columns [message #608449 is a reply to message #608437] Wed, 19 February 2014 18:32 Go to previous messageGo to next message
Solomon Yakobson
Messages: 1956
Registered: January 2010
Senior Member
BlackSwan wrote on Wed, 19 February 2014 13:22
Have two UNIQUE constraints; one on each column individually.


This will not solve the issue. It will not prevent having email same as own or other person's login and vice versa. OP could use something like:

CREATE TABLE list(
                  val      VARCHAR2(255) NOT NULL,
                  val_type VARCHAR2(5)   NOT NULL
                 )
/
ALTER TABLE list
  ADD CONSTRAINT list_pk
    PRIMARY KEY(val)
/
ALTER TABLE list
  ADD CONSTRAINT list_uk1
    UNIQUE KEY(val,val_type)
/
ALTER TABLE list
  ADD CONSTRAINT list_chk1
    CHECK(
          val_type in ('LOGIN','EMAIL')
         )
/
CREATE TABLE test_unique(
                         login      VARCHAR2(255) NOT NULL,
                         login_type VARCHAR2(5)   NOT NULL,
                         email      VARCHAR2(255),
                         email_type VARCHAR2(255)
                        )
/
ALTER TABLE test_unique
  ADD CONSTRAINT test_unique_pk
    PRIMARY KEY(login)
/
ALTER TABLE test_unique
  ADD CONSTRAINT test_unique_uk1
    UNIQUE KEY(email)
/
ALTER TABLE test_unique
  ADD CONSTRAINT test_unique_fk1
    FOREIGN KEY(login,login_type)
    REFERENCES list(val,val_type)
/
ALTER TABLE test_unique
  ADD CONSTRAINT test_unique_fk2
    FOREIGN KEY(email,email_type)
    REFERENCES list(val,val_type)
/
ALTER TABLE test_unique
  ADD CONSTRAINT test_unique_chk1
    CHECK(login_type = 'LOGIN')
/
ALTER TABLE test_unique
  ADD CONSTRAINT test_unique_chk2
    CHECK(email_type = 'EMAIL')
/




SY.

[Updated on: Wed, 19 February 2014 19:06]

Report message to a moderator

Re: Unique/check constraint on two columns [message #608461 is a reply to message #608449] Thu, 20 February 2014 00:25 Go to previous message
kojot
Messages: 27
Registered: December 2009
Location: Poland
Junior Member
Thank you Solomon for understanding my problem and the code, it really works. If it's going to be too complicated in the production code, I probably let the users to log in using only e-mail address, and I change the column 'login' to 'username', to use this value for user's posts and comments.

Thank you again,
kojot
Previous Topic: SELECT INTO causes ORA-00905 Error
Next Topic: Terrifying result of sql-query, need explanation.
Goto Forum:
  


Current Time: Fri Jul 25 21:05:05 CDT 2014

Total time taken to generate the page: 0.08924 seconds