unique between tables

[bxr556]

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.

[chrisrlong]

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

[bxr556]

can you explain to me in detail about sub-type,
I am not familar with that.

[uday]

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]

[santoshym]

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

[bxr556]

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?

[chrisrlong]

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

[santoshym]

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

[chrisrlong]

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

[santoshym]

Hi Chris,

I got it I never thought about that

Thanks