After missing to add a foreign key to a table, I ended up having to correct my mistake in many places in my PL SQL code.

Afterwards I thought: "Who created this table without this important constraint?! ... :0( Uups! It was me."

If something similar happens to you, below you'll find an SQL to add a foreign key to a table.

'References parent_table_name(parent_field)' gives the parent table, while 'Foreign Key(field_name)' is the field from the child table, which will be referenced.

If you receive an error message saying that the alter table constraint could not be validated because the table has child records, one quick fix is to delete all records in child and parent table and then executing the alter table statement again.

/* Drop a table constraint */
ALTER TABLE sod_cat_app_cat
drop constraint
fk_sod_category_app; /* Name of a constraint */

/*Add a Table Constraints for foreign key*/
Alter Table sod_cat_app_cat Modify (
	Constraint fk_sod_category_app 
	Foreign Key(sod_category_app_id)
	References sod_category_app(ID)
);

Last Updated: 24 June 2014

Hits: 10715

Oracle is able to join tables using the LIKE opertaor.

SQL

Select * 
From 
	a_Table
Left Join
	b_Table
On 
	a_Table.a_Field LIKE '%' || b_Table.b_Field || '%';

The LIKE oprator works in combination with wildcards ('%').

The above SQL will work fine for text based data types like varchar. I've never tested it for other data types.

Then on the contrary: Would it make sence to join other types than text based types using LIKE? Most likely not.

Last Updated: 26 October 2014

Hits: 9932

Recently I had to create some tables in Oracle and fill them with data from other tables for testing.

It is quite easy to create and modify tables, however it can be a pain digging through undocumented dependencies, table constraints which describe parent child relationships between tables.

In order to find foreign keys for a table, adjust below query by replacing 'table_name' with the name of a table of interest and execute.

Synonym 'a' is holding the child table details and synonym 'uc' is holding the parent table details.

Enjoy.

SQL

Last Updated: 01 January 2018

Hits: 10132