Could not create constraint. See previous errors.

Execute below query to get the error:

create table parent(id int , name varchar(30));
create table child(id int, parent_id int);
alter table child alter column id int not null;
alter table child add constraint pk primary key (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);

Msg 1776, Level 16, State 0, Line 1
There are no primary or candidate keys in the referenced table '' that match the referencing column list in the foreign key ''.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors.

Cause:
We are trying to create a FOREIGN KEY constraint on a table and the column being referenced as a FOREIGN KEY is not a
PRIMARY KEY on the other table.

SOLUTION:1

Add primary_key on referenced column.
alter table parent alter column id int not null;
alter table parent add constraint pk_parent primary key (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.

SOLUTION:2

create unique index idx_uni on parent (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.

SOLUTION:3

alter table parent add constraint uq_parent_id unique (id);
alter table child add constraint fk_child foreign key (parent_id) references parent(id);
Command(s) completed successfully.

ERROR 1093 (HY000): You can't specify target table ' ' for update in FROM clause

ERROR 1093 (HY000): You can't specify target table ' ' for update in FROM clause

This error is generated due to wrong logic used in trigger,
that we can not use the same table in trigger, which is being used in external insert / update query.

To overcome, this error, use any external function or in-built function to set the value to filed in trigger.

similar post : -

ERROR 1442 (HY000): Can't update table ' ' in stored function/trigger because it is

ERROR 1442 (HY000): Can't update table ' ' in stored function/trigger because it is

ERROR 1442 (HY000): Can't update table ' ' in stored function/trigger because it is error can be arose while creating trigger in mysql, the error cause of trying to set field value dynamically
while insert/update on same table, which is being used in trigger.

Trigger type - before/after insert

i use below step to replicate the error:

1. create table and function to generate the value for field "tree_hierarchy"

create table hierarchy ( id int , parent_id int , name varchar(20), tree_hierarchy varchar(50));
insert into hierarchy values (1, null, 'parent', 1);
insert into hierarchy values (2, 1, 'lock type', '1_2');
insert into hierarchy values (3, 2, 'personnel lock', '1_2_3');

delimiter //

CREATE FUNCTION hierarchy_sys_connect_by_path(delimiter TEXT, node INT) RETURNS TEXT
NOT DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE _path TEXT;
    DECLARE _cpath TEXT;
    DECLARE _id INT;
    DECLARE EXIT HANDLER FOR NOT FOUND RETURN _path;
    SET _id = COALESCE(node, @id);
    SET _path = _id;
    LOOP
        SELECT  IFNULL(parent_id,0)
        INTO    _id
        FROM    hierarchy
        WHERE   id = _id
                AND COALESCE(id <> @start_with, TRUE);
        SET _path = CONCAT(_id, delimiter, _path);
  END LOOP;
END
//

source -

2. create trigger, the value for field "tree_hierarchy" is set dynamically.
delimiter //

CREATE TRIGGER trigr
after INSERT ON hierarchy
FOR EACH ROW
BEGIN
        update hierarchy set tree_hierarchy =
            concat((SELECT tree_hierarchy FROM hierarchy WHERE id = NEW.parent_id),"_" , NEW.id)
        where id =NEW.id;
END
//

insert into hierarchy (id,parent_id,name) values(4, 3,'af')//

---you might got this error

ERROR 1093 (HY000): You can't specify target table ' ' for update in FROM clause 


3. another type of error can be arose by below trigger

CREATE TRIGGER trigr
after INSERT ON hierarchy
FOR EACH ROW
BEGIN
        update hierarchy set tree_hierarchy = hierarchy_sys_connect_by_path('_',NEW.parent_id) where id =NEW.id;
END
//

ERROR 1442 (HY000): Can't update table ' ' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

4. how to resolve the error

DROP TRIGGER IF EXISTS trigr//
CREATE TRIGGER trigr
before INSERT ON hierarchy 
FOR EACH ROW BEGIN 
	SET NEW.tree_hierarchy = CONCAT((SELECT tree_hierarchy FROM hierarchy WHERE id = NEW.parent_id),"_" , NEW.id); 
END 
// 
delimiter ; 

insert into hierarchy (id,parent_id,name) values(4, 3,'af');
result:- no error table has been successfully populated.

DML Autocommit in oracle

In oracle we need to do commit the DML (Data Manipulation Language) transaction explicitly.
By default DML operation are not autocommited in oracle.
But there is provision in oracle to do autocommit on for every DML transaction.

SQL>set autommit on;

After doing this, oracle will commit every dml, and data will written in datafiles.

Topic	Auto-Commit On	Auto-Commit Off
Transactions	Each statement is a separate transaction.	Commit() or rollback() completes a transaction.
Database-side JDBC routines (routines that use nested connections)	Auto-commit is turned off.	Works (no explicit commits or rollbacks are allowed).
Updatable cursors	Works for holdable cursors; does not work for non-holdable cursors.	Works.
Multiple connections accessing the same data	Works.	Works. Lower concurrency when applications use SERIALIZABLE isolation mode and table-level locking.
Updatable ResultSets	Works.	Works.
Savepoints	Does not work.	Works.

source - http://docs.oracle.com/javadb/10.8.1.2/devguide/cdevconcepts29416.html

Fact Tables and Dimension Tables

In Datawarehouse we use can use two types database :
    1. snowflake schema
    2. star schema
   
Generally we used star schema on other hand snowflake schema used very rare.

What is star schema:-
Star schema use fact tables and dimension tables. In star schema only one join
establishes the relationship between the fact table and any one of the dimension tables.A star
schema has one fact table and is associated with numerous dimensions table and depicts a star.

Fact tables:
    A fact table typically has two types of columns: those that contain numeric facts (often called measurements),
    and those that are foreign keys to dimension tables.
    A fact table contains either detail-level facts or facts that have been aggregated.
   
    In other words , if we design query for report we always use aggregate functions in select clause of query.
    The column we used in select clause    for aggregation is always taken from fact table i.e. called numeric facts.

Dimension tables:
    A dimension is a structure, often composed of one or more hierarchies, that categorizes data. Dimensional
    attributes help to describe the dimensional value. They are normally descriptive, textual values an.
    Dimension tables are generally small in size as compared to fact table.
   
    In other words, these are columns used in query for "group by" clause and used when we need some extra detail
    for the aggregate columns.
   

A example for fact ,dimension table as following:

Fact table :- Fact_UserData
Dimension table :- Dim_User, Dim_Location, Dim_Occupation

Fact_UserData
    User ID
    Location ID
    Occupation ID
    columns contains numeric data that can be meaningfully aggregated

Dim_User
    User ID
    Gender
    Ethnicity

Dim_Location
    Location ID
    District
    City
    State

Dim_Occupation
    Occupation ID
    Occupation Name

query might be like this
select
    aggregate_function(column numeric data),columns_from_dim_table
from
Fact_UserData join Dim_User on Fact_UserData.User ID=Dim_User.User ID
left outer join Dim_Location on Fact_UserData.Location ID=Dim_Location.Location ID
left outer join Dim_Occupation on Fact_UserData.Occupation ID=Dim_Occupation.Occupation ID
group by dim_table_columns
   
What is Snowflake schema:-
    The snowflake schema (sometimes called snowflake join schema) is a more complex schema
    than the star schema because the tables which describe the dimensions are normalized. It is used when a dimensional
    table becomes very big.In snow flake schema since there is relationship between the dimensions Tables it
    has to do many joins to fetch the data.Every dimension table is associated with sub dimension table.
    A star schema may be partially normalized (snowflaked), with related information stored
    in multiple related dimension tables, to support specific data warehousing needs.



OLAP data is typically stored in a star schema or snowflake schema in a relational data warehouse or in a special-purpose data management system. Measures are derived from the records in the fact table and dimensions are derived from the dimension tables.