Adding constraint to two columns

Question

I have a database table with columns "User","Role"and"Application". My requirement is that a user can only have one role but he can belong to multiple applications. I will want add this restriction in form of a constraint but i am not able to achieve it.

Setting unique constraint on "user" and "role" will not help. Also setting unique constraint on all 3 columns will also not help.

Can anyone suggest on how to set constraint here.

Edit1:

Following is the expected out:

User   Role   Application
U1     R1     A1
U1     R1     A2
U2     R2     A3

and so on... what i want to restrict is that now that U1 has been mapped to R1, U1 should not be mapped to any other Role.

how many applications that one user can relate to, i might ask? — Muhamad Iqbal, Dec 05 '14 at 04:06
Are you allowed to create triggers on the table? Or fast-refreshable materialized views? — peter.hrasko.sk, Dec 09 '14 at 20:50

score 0 · Answer 1 · answered Dec 05 '14 at 06:29

0

Then you need to change your table design and normalize it further. If the relationship between USER and ROLE is 1-to-1 then have a lookup table for user_role_mapping and create another table for user_application_mapping.

Ideally your two tables would look like :

Create table user_role_map
   (
    user <datatype>,
    role <datatype>
    constraint user_pk primary key (user, role)
   );

Create table user_application_map
   (
    user         <datatype> NOT NULL,
    applications <datatype> NOT NULL
   );

answered Dec 05 '14 at 06:29

Lalit Kumar B

47,486
13
97
124

Agreed but that option is not available. – Lokesh Dec 05 '14 at 10:43
Then simply have unique constraint on user and role. Simple! – Lalit Kumar B Dec 05 '14 at 10:45
That will not work, as i said a user with same role can belong to multiple apps, which would mean that user-role row will have to be repeated for each app. – Lokesh Dec 05 '14 at 10:47
You are confusing between the unique combination and repetition of the same combination. Can you post some example and show your desired output. – Lalit Kumar B Dec 05 '14 at 10:53
so you mean altering the schema is out of the option, and the answer's scope is only around this table only? – Muhamad Iqbal Dec 05 '14 at 12:41
@LalitKumarB: Added sample output – Lokesh Dec 05 '14 at 16:34
@nobodykid: Exactly, i need to put a constraint only. – Lokesh Dec 05 '14 at 16:36
Are you allowed to create triggers on the table? – peter.hrasko.sk Dec 05 '14 at 16:38
I insist, change your design first. Else, you would waste time on such things. – Lalit Kumar B Dec 05 '14 at 16:39
@nop77svk, don't forget the fact that triggers are always a side-effect. And in OP's case it would be even worse. OP must deal with the design issue first. – Lalit Kumar B Dec 05 '14 at 16:42
@LalitKumarB, yes, OP has issue with design. But he **clearly** stated that altering the data model is out of question, so I'm thinking about other options. – peter.hrasko.sk Dec 05 '14 at 16:47

Adding constraint to two columns

1 Answers1