unique between tables
DBAsupport.com Forums - Powered by vBulletin
Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: unique between tables

  1. #1
    Join Date
    Jan 2000
    Location
    san jose
    Posts
    149
    I have table

    dept ( deptname primary key, col1, col2);
    emp ( empname primary key, c1,c2);

    i have two tables , because of our application's limitation.
    they ask me to implement inside the database that
    deptname never the same as empname.

    although it sounds ridiculous, but it is the way it is,
    so i need to do some trigger or whatever to ensure no
    dept name and emp name are the same,

    how to do that?

    what i can thought is each time just before i try to insert something to dept table, i will check to see if the same value
    already exist in emp table. visa versa.
    if this thought is correct, can somebody write one for me,
    because i don't know EXACTLY how to write it.

  2. #2
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Sorry, but you can't get rid of me as easy as deleting the old thread and starting a new one

    *If* the requirement is truly as simple as you have explained it, then it is stupid and should not be implemented. You need to argue better .

    However, as your example uses the standard emp/dept tables, I'll assume you made up yet another sample problem rather than giving us the true details.

    So again, I would say that the first thing to check is if what you truly have is a sub-type relationship - you didn't clearly answer me last time. If it is a sub-type, then model it that way.

    Second, are these really name fields? Or are was talking about the PKs? Because, as I've ranted about before , I'm somewhat opposed to the general use of user-supplied text fields for PKs. Now, if it is actually the PK that cannot be the same, someone on the last thread suggested a single sequence that would feed both tables, which is the best solutions *assuming* that you really don't have a sub-type relationship, which I still suspect.

    If, however, you are truly talking about names, then you *do* have an issue. Again, I would talk them out of it because it sounds ridiculous. Barring that, the best solution off the top of my head would be to implement a sub-type relation 'of sorts' anyway - even if one doesn't truly exist. Make a table that just holds the name. Make a unique index on that field. Implement triggers on both 'child' tables to insert/update/delete that field when they update their own key. But one way or another, this is going to cause you issues somewhere down the road, so make sure you have exhausted the other options (sub-type, removal, Sequence) before going down this path.

    HTH,

    - Chris

  3. #3
    Join Date
    Jan 2000
    Location
    san jose
    Posts
    149
    can you explain to me in detail about sub-type,
    I am not familar with that.

  4. #4
    Join Date
    Feb 2001
    Posts
    163
    bxr,

    I cannot come up with even / odd answer this time since you switched them to varchar. If you want to still push your concept through, you can Try using Upper case and Lower Case


    uday

    [Edited by uday on 05-15-2001 at 10:05 PM]

  5. #5
    Join Date
    Mar 2001
    Posts
    635
    Hi

    I have created following tables

    emp

    empname primary key

    dept

    deptname primary key

    I have written two triggers one for emp table and the other for dept table I feel you dont want deptname to have the same values which are there in empname and vice versa I have tested these triggers only once before you implement on ur production please test the code rigorously and change it according to ur environment

    Employee table trigger

    create or replace trigger emp_trigger
    before insert or update on emp for
    each row
    declare
    deptname char(20);
    begin
    select deptname into deptname from dept where
    dept.deptname = :new.empname;
    if sql%found
    then
    raise_application_error(-20000,'Empname cannot be the same as deptname');
    else
    null;
    end if;
    exception
    when no_data_found
    then
    null;
    end;


    Dept table trigger

    create or replace trigger dept_trigger
    before insert or update on dept for
    each row
    declare
    empname char(20);
    begin
    select empname into empname from emp where
    emp.empname = :new.deptname;
    if sql%found
    then
    raise_application_error(-20000,'Deptname cannot be the same as Empname');
    else
    null;
    end if;
    exception
    when no_data_found
    then
    null;
    end;


    I Hope this is what you are looking for and it helps

    Regards
    Santosh

  6. #6
    Join Date
    Jan 2000
    Location
    san jose
    Posts
    149
    yes, such a trigger is what i want,
    however, what if when you check

    select deptname into deptname from dept where
    dept.deptname = :new.empname;

    you found nothing and just then somebody insert a new
    record with the same value?


  7. #7
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    Yes, that would be the problem with that solution. You can't *guarantee* uniqueness without a unique index.

    Now, back to the sub-type issue. I explained this before - in the thread you deleted. Of course, I *still* don't know if what you have is a sub-type or not as you *still* have not given the real business problem, but anyway...

    Suppose we are modelling the contracts for a consulting firm. They have multiple types of contracts - we will focus on 'Fixed Price' and 'Time and Materials' contracts. These two types of contracts contain very different data. Therefore, one might be tempted to model them as 2 separate tables. Of course, somewhere along the line, one would realize that a singular contract_id is necessary for RI in some other table. So, the next thought might be to somehow enforce that the 2 ids in the 2 tables be unique - *like your example*. The whole problem is that the business problem was not modeled correctly. What we actually have is 1 entity - a *Contract*. There simply happen to be different 'flavors' or 'types' of contracts.

    Therefore, what we want is a single Contract_T table. This table has Contract_ID as its PK. It also has all the fields that are common across *all* contract types (who it's with, when it started, yada yada). It also has a field (ContractType_Cd) to specify exactly which type of contract we are dealing with. This field tells us where to look for the 'rest' of the data about that contract.

    The 'rest' of the data is stored in 'sub-type' tables. These table have 'sub-type' relationships with Contract_T. This is really a logical concept. In physical terms, they have 1:0,1 relationships with the parent, Contract_T. We have ContractTandM_T and ContractFixedPrice_T. Each of these tables also have Contract_ID as their PK. Each then holds the data that is specific to each type of contract (that is not already stored in Contract_T).

    Does this make sense? If not, start checking around the net for more info.
    Here is one link:
    http://www.datamodel.org/DataModelDictionary.html

    HTH,

    - Chris

  8. #8
    Join Date
    Mar 2001
    Posts
    635
    Hi

    I have written One trigger for each of the tables so I feel the uniquness stays in both the tables you can try from both the tables.Maybe I might be wrong in this case can you give me a better explanation about this

    Regards
    Santosh

  9. #9
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    The problem is that it is not multi-user-safe.

    If, at the same time, 2 users try to add entries with the same name in the different tables, it fails.

    User 1__________________User2
    ----------------_________-------------------
    Insert Fred_______________Insert Fred
    ___in dept__________________in emp
    Trigger fires____________Trigger fires
    SELECT - no Fred _________SELECT - No Fred
    ___in emp__________________in dept
    Inserts Fred____________Insert Fred
    ___in dept__________________in emp
    Commit__________________Commit

    We end up with a Fred in both tables.

    Make sense?

    As I said before, the only way to guarantee uniqueness is through a unique index. Therefore, as I said, you have to make a single table to hold the name and put a unique index on it. You then need insert/update/delete triggers on the 2 target tables that insert/update/delete the table with unique index. With such a solution, user 2 would have gotten a Unique Key violation when he tried to insert the second Fred.

    Make sense?

    - Chris

  10. #10
    Join Date
    Mar 2001
    Posts
    635
    Hi Chris,

    I got it I never thought about that

    Thanks
    Santosh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width