kadang-kadang bila kesedihan datang singgah dalam diri kita
membuat kita duduk termenung berfikir
dalam keadaan kita sedih, saat itulah hati kita tersentuh.
membuat kita sedar dari kelalaian.
kembalikan kita ke jalan yang benar.
programming
Monday, July 4, 2011
Monday, September 27, 2010
Integrity constraint
Oracle/PLSQL: ORA-02291 Error
Error : ORA-02291: integrity constraint <constraint name> violated - parent key not found
Cause : You tried to reference a table using a unique or primary key, but the columns that you listed did not match the primary key, or a primary key does not exist for this table.
Action :
The options to resolve this Oracle error are:
Error : ORA-02291: integrity constraint <constraint name> violated - parent key not found
Cause : You tried to reference a table using a unique or primary key, but the columns that you listed did not match the primary key, or a primary key does not exist for this table.
Action :
The options to resolve this Oracle error are:
- This error commonly occurs when you have a parent-child relationship established between two tables through a foreign key. You then have tried to insert a value into the child table, but the corresponding value does not exist in the parent table.
To correct this problem, you need to insert the value into the parent table first and then you can insert the corresponding value into the child table.
For example, if you had created the following foreign key (parent-child relationship).
CREATE TABLE supplier ( supplier_id numeric(10) not null,
supplier_name varchar2(50) not null,
contact_name varchar2(50),
CONSTRAINT supplier_pk PRIMARY KEY (supplier_id) );
CREATE TABLE products ( product_id numeric(10) not null,
supplier_id numeric(10) not null,
CONSTRAINT fk_supplier
FOREIGN KEY (supplier_id)
REFERENCES supplier (supplier_id) );
Then you try inserting into the products table as follows:
INSERT INTO products
(product_id, supplier_id)
VALUES (1001, 5000);
You would receive the following error message:
Since the supplier_id value of 5000 does not yet exist in the supplier table, you need to first insert a record into the supplier table as follows:
INSERT INTO supplierThen you can insert into the products table:
(supplier_id, supplier_name, contact_name)
VALUES (5000, 'Microsoft', 'Bill Gates');
INSERT INTO products
(product_id, supplier_id)
VALUES (1001, 5000);
Monday, September 20, 2010
Decode Function
Decode Function
In Oracle/PLSQL, the decode function has the functionality of an IF-THEN-ELSE statement.
The syntax for the decode function is:
decode( expression , search , result [, search , result]... [, default] )expression is the value to compare.
search is the value that is compared against expression.
result is the value returned, if expression is equal to search.
default is optional. If no matches are found, the decode will return default. If default is omitted, then the decode statement will return null (if no matches are found).
Applies To:
- Oracle 9i, Oracle 10g, Oracle 11g
For example:
You could use the decode function in an SQL statement as follows:
SELECT supplier_name, decode(supplier_id, 10000, 'IBM',
10001, 'Microsoft',
10002, 'Hewlett Packard',
'Gateway') result FROM suppliers;
The above decode statement is equivalent to the following IF-THEN-ELSE statement:
IF supplier_id = 10000 THEN
result := 'IBM';
ELSIF supplier_id = 10001 THEN
result := 'Microsoft';
ELSIF supplier_id = 10002 THEN
result := 'Hewlett Packard';
ELSE
result := 'Gateway';
END IF;
The decode function will compare each supplier_id value, one by one.
Subscribe to:
Posts (Atom)