putting constraints

[Lorey]

Hi experts!

Please help on the ff:

I have an existing table :
table1 with column:
pkey1,
pkey2,
pkey3

I am creating a new table2 with column :
pkey,
pkey1 (which is the same as table1.pkey2)

the problem is how can i put constraints on table2 that only table1.pkey2 with specific value specified in table1.pkey3
should be entered in table2.


lorey

 

[SantaMufasa]

Lorey,

I infer from your description that you want some sort of a Primary Key / Foreign Key relationship between Table1 and Table2, is that correct?

If you want a database-defined Foreign Key in Table2, then the Foreign Key must point to a column (or set of columns) that is/are defined as a Primary Key or Unique Key in Table1.

There are no exceptions or workarounds available in Oracle to the above rule for Foreign Key Definitions.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[Lorey]

For example,

Table1
Pkey Value Type
1 mft1 console
2 sess1 session
3 mft2 console
4 sess2 session

Table2
Pkey ValueKey (from table1.value) Profilekey
1 1 10002
2 3 10003

I only want to make sure that entries in table 2 will be
the entries in table 1 with type "console"

 

[jaxtell]

Lorey,
I think your best bet is a trigger on Table2. On insert or update, verify the data meets your needs. If it doesn't, then raise an exception.

-----------------------------------------
I cannot be bought. Find leasing information at

http://www.joshaxtell.com/

 

[Lorey]

Hi jaxtell,

That's a good idea.
If its not too much to ask, could you please help me contruct the trigger script. I'm an entry level in DB.

Thanks in advance !

lorey

 

[SantaMufasa]

Sorry, Lorey, I have been away from my computer since last Friday evening (when you last posted).

Here is a sample trigger that does, I believe, what you want:

create or replace trigger tr_table2_validate
 after INSERT or UPDATE on TABLE2
   for each row
    declare
        type_label table1.typ%type;
    begin
        select typ into type_label
          from table1
         where :new.valuekey = pkey;
        if type_label <> 'console' then
            raise_application_error (-20000,'Type that corresponds to ValueKey "'||
                :new.valuekey||'" is "'||type_label||'". Types must = "console".');
        end if;
    end;
/

Trigger created.

SQL> select * from table1;

PKEY VALUE      TYP
---- ---------- -------
   1 mft1       console
   2 sess1      session
   3 mft2       console
   4 sess2      session

4 rows selected.

SQL> select * from table2;

no rows selected

SQL> insert into table2 values (1,1,10002);

1 row created.

SQL> insert into table2 values (2,3,10003);

1 row created.

SQL> insert into table2 values (3,2,10004);
insert into table2 values (3,2,10004)
            *
ERROR at line 1:
ORA-20000: Type that corresponds to ValueKey "2" is "session". Types must = "console".
ORA-06512: at "TEST.TR_TABLE2_VALIDATE", line 8
ORA-04088: error during execution of trigger 'TEST.TR_TABLE2_VALIDATE'

SQL> select * from table2;

      PKEY   VALUEKEY PROFILEKEY
---------- ---------- ----------
         1          1      10002
         2          3      10003

2 rows selected.

Let us know if this is what you want.


Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]