automatically updating in child table

[mgl70]

Hi ,
I have a problem.I have a mastertable and child table.I need to update a column in mastertable which has a foreign key in child table. When I am updating it is giving error 'unique constraint violated'.

How to update the child table column automatically when I update master table column. Then it wont give error.

Please help on this.
Thanks.

 

[Turkbear]

Hi,
Normally, that error is unrelated to the 'child' table..It means that the value you are trying to inset already exists in the table you are inserting into.






To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[SantaMufasa]

There are Seven Rules for Well-Behaved Primary Keys:

1) Universal: Every row has a value in that column; not null.

2) Unique: No two rows can share the same value

3) Short as possible while preserving uniqueness: The shorter the possible, the less space it takes to store in the PK column, then the savings are multiplied by each of the foreign key (FK) references.

4) Numeric: Numeric values take roughly half the storage space as beyond-numeric values. Numeric, by definition means shorter, thus supporting Rule #3.

5) Not subject to change: If a PK value must change, then all of the FK references must change, as well. Avoidable change is a processing waste.

6) "Ours": You do not want to use someone else's PK scheme...If their scheme changes, so must your scheme, thus breaking Rule #5.

7) "Stupid": Good Primary Keys should not be intelligent. Specifically, they should not contain intelligence or have any special meaning...Meanings can change, therefore again breaking Rule #5.

I'm guessing ("betting" actually), that, at minimum your Primary Keys are disobeying Rules 2, 5, and 7; they very possibly also disobey Rules 3, 4, and 6.

These rules are in place to avoid the very problems that you are encountering. Said another way, If you follow these rules, you will not have the problems you are having. "Rules are our friends."

Now, since I have been accused of being rather dogmatic about good-design rules, and not helping people when their poor designs disobey rules of good design, I'll offer some help with your problem:

1) Apparently, you are attempting to give a row a Primary Key value that matches another exiting Primary Key value on another row in your table (thus disobeying Rule 2, above). Therefore, if you have your heart set about giving the new row the value of another PK in your table, you must first change the other PK value (and all of the FK values referenced in its child tables, as well) to a unique PK value.

2) Then you can successfully change the current row's PK value (and all of the FK values referenced in its child tables, as well).

My blood pressure just went up 50 points by my actually making this abhorrent suggestion.

Let us know what you decide.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[BJCooperIT]

Dave,
Well done. Now take a deep breath and exhale slowly...



^{Beware of false knowledge; it is more dangerous than ignorance. ~George Bernard Shaw}
Consultant Developer/Analyst Oracle, Forms, Reports & PL/SQL (Windows)
Author and Sole Proprietor of: Emu Products Plus

 

[mgl70]

Thank you very much Turk. Yes. I am trying to enter the same value (unique constraint error). Again I tried with diff value , it gave "child record' error'.

Also Thanks to Santa. reg 5th rule means If I update a primary column in a master table, we have to update all the correcsponding foreign key columns in child tables.So automatically we canot update in child tables.ok.

I have one more question. This is not related to referential integrity.

If I want to delete a column in a table, how can I delete it. DELETE means it deletes the entire row. But I need to delete perticular column(NULL column) in a row.

Is this the good way that I can update the column to a 'blank space' or 'null' since it is a null column.

Please give a advice.

Thanks.

 

[SantaMufasa]

MGL,

To cause a particular column on a row (i.e., a cell) to become NULL, you can say:

UPDATE <table_name> SET <column_name> = NULL
 WHERE <some condition>;

This effectively "deletes" the value in that cell while preserving the rest of the data on the row. In the Oracle World, you NEVER should set a column's value to one or more blank spaces; you should always use NULL to achieve that logical effect.

To get rid of all values in a particular column for all rows, you do the same command, but forget the WHERE clause.

Let us know if this answers your question(s).

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[mgl70]

Thanks Santa. First I tested with blank space and null. It worked.Setting with blank space does not make sense.

For logical effect I set to NULL only for future purposes.

Thanks for your help and time.

 

[sem]

If you do need it, you may remove a column by dropping it.

Regards, Dima

http://seminihin.narod.ru