Foreign Key Constraint in MySql





InnoDB supports foreign key constraints. The syntax for a foreign key constraint definition in InnoDB looks like this:


[CONSTRAINT [symbol]] FOREIGN KEY
[index_name] (index_col_name, ...)
REFERENCES tbl_name (index_col_name,...)
[ON DELETE reference_option]
[ON UPDATE reference_option]

reference_option:
RESTRICT | CASCADE | SET NULL | NO ACTION


index_name represents a foreign key ID. If given, this is ignored if an index for the foreign key is defined explicitly. Otherwise, if creates an index for the foreign key, it uses index_name for the index name.

Foreign keys definitions are subject to the following conditions:

  • Both tables must be tables and they must not be TEMPORARY tables.

  • Corresponding columns in the foreign key and the referenced key must have similar internal data types insideInnoDB so that they can be compared without a type conversion. The size and sign of integer types must be the same. The length of string types need not be the same. For nonbinary (character) string columns, the character set and collation must be the same.

  • InnoDB requires indexes on foreign keys and referenced keys so that foreign key checks can be fast and not require a table scan. In the referencing table, there must be an index where the foreign key columns are listed as the first columns in the same order. Such an index is created on the referencing table automatically if it does not exist. (This is in contrast to some older versions, in which indexes had to be created explicitly or the creation of foreign key constraints would fail.) index_name, if given, is used as described previously.

  • InnoDB allows a foreign key to reference any index column or group of columns. However, in the referenced table, there must be an index where the referenced columns are listed as the columns in the same order.

  • Index prefixes on foreign key columns are not supported. One consequence of this is that BLOB and TEXT columns cannot be included in a foreign key because indexes on those columns must always include a prefix length.

  • If the CONSTRAINT symbol clause is given, the symbol value must be unique in the database. If the clause is not given, InnoDB creates the name automatically.

InnoDB rejects any INSERT or UPDATE operation that attempts to create a foreign key value in a child table if there is no a matching candidate key value in the parent table. The action InnoDB takes for any UPDATE or DELETE operation that attempts to update or delete a candidate key value in the parent table that has some matching rows in the child table is dependent on the specified using ON UPDATE and ON DELETE subclauses of the FOREIGN KEY clause. When the user attempts to delete or update a row from a parent table, and there are one or more matching rows in the child table, InnoDB supports five options regarding the action to be taken. If ON DELETE or ON UPDATE are not specified, the default action is RESTRICT.

  • CASCADE: Delete or update the row from the parent table and automatically delete or update the matching rows in the child table. Both ON DELETE CASCADE and ON UPDATE CASCADE are supported. Between two tables, you should not define several ON UPDATE CASCADE clauses that act on the same column in the parent table or in the child table.

    Note

    Currently, cascaded foreign key actions to not activate triggers.

  • SET NULL: Delete or update the row from the parent table and set the foreign key column or columns in the child table to NULL. This is valid only if the foreign key columns do not have the NOT NULL qualifier specified. Both ON DELETE SET NULL and ON UPDATE SET NULL clauses are supported.

    If you specify a SET NULL action, make sure that you have not declared the columns in the child table as NOT NULL.

  • NO ACTION: In standard SQL, NO ACTION means no action in the sense that an attempt to delete or update a primary key value is not allowed to proceed if there is a related foreign key value in the referenced table. InnoDB rejects the delete or update operation for the parent table.

  • RESTRICT: Rejects the delete or update operation for the parent table. Specifying RESTRICT (or NO ACTION) is the same as omitting the ON DELETE or ON UPDATE clause. (Some database systems have deferred checks, andNO ACTION is a deferred check. In MySQL, foreign key constraints are checked immediately, so NO ACTION is the same as RESTRICT.)

  • SET DEFAULT: This action is recognized by the parser, but InnoDB rejects table definitions containing ON DELETE SET DEFAULT or ON UPDATE SET DEFAULT clauses.

InnoDB supports foreign key references within a table. In these cases, “child table records” really refers to dependent records within the same table.

Here is a simple example that relates parent and child tables through a single-column foreign key:


CREATE TABLE parent (id INT NOT NULL,
PRIMARY KEY (id)
) ENGINE=INNODB;

CREATE TABLE child (id INT, parent_id INT,
INDEX par_ind (parent_id),
FOREIGN KEY (parent_id) REFERENCES parent(id)
ON DELETE CASCADE
) ENGINE=INNODB;


A more complex example in which a product_order table has foreign keys for two other tables. One foreign key references a two-column index in the product table. The other references a single-column index in the customertable:

CREATE TABLE product (category INT NOT NULL, id INT NOT NULL,
price DECIMAL,
PRIMARY KEY(category, id)) ENGINE=INNODB;

CREATE TABLE customer (id INT NOT NULL,
PRIMARY KEY (id)) ENGINE=INNODB;

CREATE TABLE product_order (no INT NOT NULL AUTO_INCREMENT,
product_category INT NOT NULL,
product_id INT NOT NULL,
customer_id INT NOT NULL,
PRIMARY KEY(no),
INDEX (product_category, product_id),
FOREIGN KEY (product_category, product_id)
REFERENCES product(category, id)
ON UPDATE CASCADE ON DELETE RESTRICT,
INDEX (customer_id),
FOREIGN KEY (customer_id)
REFERENCES customer(id)) ENGINE=INNODB;

source mysql.com

League of Legends : DOTA Replacement







Developer Riot Games announces new MMO game titled League of Legends which combines aspects of role-playing and strategy games popular as DOTA all star. Was released on the public after this game can be a substitute for DOTA? Only time can talk. Welcome to the new round of competitive online gaming.

You will act as a Summoner who can bring a hero to champion the battle. Together with the NPC ally, you must use tactics to defeat the other in a battle Summoner very similar to DotA.

This latest online game, is really similar to DOTA all star, the difference, the DOTA is a mod of warcraft3 while, the League of Legends is an online game actually stand alone.Just like DotA, League of Legends in this, your team will control the Hero in tactics and strategy in defeating the other team's hero.Each hero class has its own unique skills.

For business graphics, new games are obviously better than DotA game that was released many years. But whether this game could replace DOTA??







download league of legends

JAVA MATRIX CALCULATOR

Java Web Services: Up and Running

No special classes or libraries are used with this application. The complete source resides in the one file above. After downloading, the following should work in any JDK 1.2 compatible compiler:

javac MatrixCalculator.java
java MatrixCalculator

Matrix Calculator Tips/Help

All Matrices must be symmetric (n x n)
Enter Matrix Elements Row by Row seperated by spaces.
Ex. (2x2)

2 4

3 1


Results will be placed in the C matrix.

The calculation of the determinant, by definition, is based upon a factorial number of calculations with respect to the size of the matrix. ie. a 3x3 matrix would have 6 calculations (3!) to make, whereas a 20x20 matrix would have 2.43 x 10^18 calculations (20!). So instead of brute forcing the calculations, I first do some operations on the matrix, which converts it to a upper triangular matrix, and then calculate the determinant by multipling down the diagonal, since everything below is 0, this will give the determinant.


Floating Points and Accuracies

For some reason computers aren't as accurate as I think they are, probably my calculation techniques. The accuracy of the numbers are probably only to 3 maybe 2 decimal places. If you keep applying operations to matrices and then use the resultant matrix a couple of times, the decimals get out of whack. Calculating an inverse and then multplying the matrix by it, is a good example of this.



Test Some Mathematical Theories

The determinant of A-inverse equals 1 over the determinant of A.

If two rows of matrix A are equal, the determinant of A equals 0.

det(A*B)=det(A)det(B)

A*B does not necessarily equal B*A

The determinant of A-transpose equals the determinant of A.

If the matrix B is constructed by interchanging two rows (columns) in matrix A, then the determinant of B equals the negative determinant of A

You can test, adj(A) = det(A) * inv(A), but this is the theorem I use to calculate the inverse, so it better work.



Mathematics and Linear Algebra Calculating the Determinant

The calculation of the determinant, by definition, is based upon a factorial number of calculations with respect to the size of the matrix. ie. a 3x3 matrix would have 6 calculations (3!), whereas a 20x20 matrix would have 2.43 x 10^18 calculations (20!).

So instead of brute forcing the calculations, I first do some operations on the matrix, which converts it to a upper triangular matrix, and then calculate the determinant by multipling down the diagonal, since everything below is 0, this will give the determinant.

See The Mathematics Behind Them for more information and mathematical explanations on the definitions and calculation techniques.



Click the spoiler below for displaying source-code





SOURCE CODE



import java.awt.BorderLayout;

import java.awt.Component;

import java.awt.Dimension;

import java.awt.GridLayout;

import java.awt.event.ActionEvent;

import java.awt.event.ActionListener;

import java.awt.event.WindowAdapter;


import java.awt.event.WindowEvent;

import java.text.NumberFormat;

import java.util.ArrayList;

import java.util.StringTokenizer;



import javax.swing.BorderFactory;

import javax.swing.Box;

import javax.swing.BoxLayout;

import javax.swing.JButton;


import javax.swing.JFrame;

import javax.swing.JLabel;

import javax.swing.JPanel;

import javax.swing.JScrollPane;

import javax.swing.JTextArea;



public class MatrixCalculator {



private boolean DEBUG = true;




private boolean INFO = true;



private static int max = 100;



private static int decimals = 3;



private JLabel statusBar;



private JTextArea taA, taB, taC;



private int iDF = 0;




private int n = 4;



private static NumberFormat nf;



public Component createComponents() {



/* == MATRIKS == */

taA = new JTextArea();

taB = new JTextArea();

taC = new JTextArea();




JPanel paneMs = new JPanel();

paneMs.setLayout(new BoxLayout(paneMs, BoxLayout.X_AXIS));

paneMs.setBorder(BorderFactory.createEmptyBorder(10, 10, 10, 10));

paneMs.add(MatrixPane("Matrix A", taA));

paneMs.add(Box.createRigidArea(new Dimension(10, 0)));

paneMs.add(MatrixPane("Matrix B", taB));

paneMs.add(Box.createRigidArea(new Dimension(10, 0)));

paneMs.add(MatrixPane("Matrix C", taC));




/* == OPERATION BUTTONS == */

JPanel paneBtn = new JPanel();

paneBtn.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));

paneBtn.setLayout(new GridLayout(3, 3));

JButton btnApB = new JButton("A + B = C");

JButton btnAmB = new JButton("A * B = C");

JButton btnBmA = new JButton("B * A = C");

JButton btnAdjA = new JButton("adjoint(A) = C");


JButton btnInvA = new JButton("inverse(A) = C");

JButton btnInvB = new JButton("inverse(B) = C");

JButton btnTrnsA = new JButton("transpose(A) = C");

JButton btnDetA = new JButton("determ(A) = C");

JButton btnDetB = new JButton("determ(B) = C");

paneBtn.add(btnApB);

paneBtn.add(btnAmB);

paneBtn.add(btnBmA);

paneBtn.add(btnAdjA);


paneBtn.add(btnInvA);

paneBtn.add(btnInvB);

paneBtn.add(btnTrnsA);

paneBtn.add(btnDetA);

paneBtn.add(btnDetB);



/* == ADD BUTTON Listeners == */

btnApB.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent evt) {


try {

DisplayMatrix(AddMatrix(ReadInMatrix(taA),

ReadInMatrix(taB)), taC);

} catch (Exception e) {

System.err.println("Error: " + e);

}

}

});




btnAmB.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent evt) {

try {

DisplayMatrix(MultiplyMatrix(

ReadInMatrixNotSquare(taA),

ReadInMatrixNotSquare(taB)), taC);

} catch (Exception e) {

System.err.println("Error: " + e);

}


}

});



btnBmA.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent evt) {

try {

DisplayMatrix(MultiplyMatrix(ReadInMatrixNotSquare(taB),

ReadInMatrixNotSquare(taA)), taC);

} catch (Exception e) {


System.err.println("Error: " + e);

}

}

});



btnInvA.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent evt) {

try {

DisplayMatrix(Inverse(ReadInMatrix(taA)), taC);


} catch (Exception e) {

System.err.println("Error: " + e);

}

}

});



btnInvB.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent evt) {

try {


DisplayMatrix(Inverse(ReadInMatrix(taB)), taC);

} catch (Exception e) {

System.err.println("Error: " + e);

}

}

});



btnAdjA.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent evt) {


try {

DisplayMatrix(Adjoint(ReadInMatrix(taA)), taC);

} catch (Exception e) {

System.err.println("Error: " + e);

}

}

});



btnTrnsA.addActionListener(new ActionListener() {


public void actionPerformed(ActionEvent evt) {

try {

DisplayMatrix(Transpose(ReadInMatrixNotSquare(taA)), taC);

} catch (Exception e) {

System.err.println("Error: " + e);

}

}

});




btnDetA.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent evt) {

try {

taC.setText("Determinant A: "

+ nf.format(Determinant(ReadInMatrix(taA))));

} catch (Exception e) {

System.err.println("Error: " + e);

}

}


});



btnDetB.addActionListener(new ActionListener() {

public void actionPerformed(ActionEvent evt) {

try {

taC.setText("Determinant B: "

+ nf.format(Determinant(ReadInMatrix(taB))));

} catch (Exception e) {

System.err.println("Error: " + e);


}

}

});



/* == MAIN PANEL == */

JPanel pane = new JPanel();

pane.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));

pane.setLayout(new BoxLayout(pane, BoxLayout.Y_AXIS));

pane.add(paneMs);


pane.add(paneBtn);



JPanel fpane = new JPanel();

fpane.setLayout(new BorderLayout());

fpane.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));

fpane.add("Center", pane);

statusBar = new JLabel("Ready");

fpane.add("South", statusBar);



return fpane;


}



/* == Setup Invidual Matrix Panes == */

private JPanel MatrixPane(String str, JTextArea ta) {

JScrollPane scrollPane = new JScrollPane(ta);

int size = 200;



scrollPane.setPreferredSize(new Dimension(size, size));

JLabel label = new JLabel(str);

label.setLabelFor(scrollPane);




JPanel pane = new JPanel();

pane.setBorder(BorderFactory.createEmptyBorder(5, 5, 5, 5));

pane.setLayout(new BoxLayout(pane, BoxLayout.Y_AXIS));

pane.add(label);

pane.add(scrollPane);



return pane;

}




public static void main(String[] args) {

JFrame frame = new JFrame("Matrix Calculator");

frame.setSize(new Dimension(725, 200));

MatrixCalculator app = new MatrixCalculator();



Component contents = app.createComponents();

frame.getContentPane().add(contents, BorderLayout.CENTER);

frame.addWindowListener(new WindowAdapter() {

public void windowClosing(WindowEvent e) {


System.exit(0);

}

});

frame.pack();

frame.setVisible(true);



nf = NumberFormat.getInstance();

nf.setMinimumFractionDigits(1);

nf.setMaximumFractionDigits(decimals);




}



// ------------------------------------------------------------------------------

// ------------------------------------------------------------------------------







public float[][] ReadInMatrix(JTextArea ta) throws Exception {

if (DEBUG) {

System.out.println("Reading In Matrix");


}



/* == Parse Text Area == */

String rawtext = ta.getText();

String val = "";

int i = 0;

int j = 0;

int[] rsize = new int[max];



/* == Determine Matrix Size/Valid == */


StringTokenizer ts = new StringTokenizer(rawtext, "\n");

while (ts.hasMoreTokens()) {

StringTokenizer ts2 = new StringTokenizer(ts.nextToken());

while (ts2.hasMoreTokens()) {

ts2.nextToken();

j++;

}

rsize[i] = j;

i++;


j = 0;

}

statusBar.setText("Ukuran Matriks: " + i);

if ((DEBUG) || (INFO)) {

System.out.println("Ukuran Matriks: " + i);

}



for (int c = 0; c < i; c++) {
if (DEBUG) {
System.out.println("i=" + i + " j=" + rsize[c] + " Kolom: "
+ c);
}

if (rsize[c] != i) {
statusBar.setText("Invalid Matrix. Size Mismatch.");
throw new Exception("Invalid Matrix. Size Mismatch.");
}
}
/* == set ukuran matriks == */
n = i;

float matrix[][] = new float[n][n];
i = j = 0;
val = "";

/* == Actual Parsing == */
StringTokenizer st = new StringTokenizer(rawtext, "\n");
while (st.hasMoreTokens()) {
StringTokenizer st2 = new StringTokenizer(st.nextToken());
while (st2.hasMoreTokens()) {
val = st2.nextToken();
try {
matrix[i][j] = Float.valueOf(val).floatValue();
} catch (Exception exception) {
statusBar.setText("Invalid Number Format");
}
j++;
}
i++;
j = 0;
}

if (DEBUG) {
System.out.println("Baca Matriks::");
for (i = 0; i < n; i++) {
for (j = 0; j < n; j++) {
System.out.print("m[" + i + "][" + j + "] = "
+ matrix[i][j] + " ");
}
System.out.println();
}
}

return matrix;
}

public float[][] ReadInMatrixNotSquare(JTextArea ta)
throws Exception {
if (DEBUG) {
System.out.println("Membaca Matriks");
}

/* == Parse Text Area == */
String rawtext = ta.getText();

/* == Determine Matrix Size/Valid == */
StringTokenizer ts = new StringTokenizer(rawtext, "\n");

if (DEBUG)
System.out.println("Baris: " + ts.countTokens());

float matrix[][] = new float[ts.countTokens()][];

StringTokenizer st2;
int row = 0;
int col = 0;
//making sure rows are same length
int last = -5;
int curr = -5;
while (ts.hasMoreTokens()) {
st2 = new StringTokenizer(ts.nextToken(), " ");
last = curr;
curr = st2.countTokens();
if(last != -5 && curr!= last)
throw new Exception("Baris != length");
if (DEBUG)
System.out.println("Kolom: " + st2.countTokens());
matrix[row] = new float[st2.countTokens()];
while (st2.hasMoreElements()) {
matrix[row][col++] = Float.parseFloat(st2.nextToken());
}
row++;
col = 0;
}
System.out.println();
return matrix;
}

// --------------------------------------------------------------
// Display Matrix in TextArea
// --------------------------------------------------------------
public void DisplayMatrix(float[][] matrix, JTextArea ta) {

if (DEBUG) {
System.out.println("Displaying Matrix");
}

String rstr = "";
String dv = "";

for (int i = 0; i < matrix.length; i++) {
for (int j = 0; j < matrix[i].length; j++) {
dv = nf.format(matrix[i][j]);
rstr = rstr.concat(dv + " ");
}

rstr = rstr.concat("\n");
}

ta.setText(rstr);
}

public float[][] AddMatrix(float[][] a, float[][] b) throws Exception {
int tms = a.length;
int tmsB = b.length;
if (tms != tmsB) {
statusBar.setText("Matrix Size Mismatch");
}

float matrix[][] = new float[tms][tms];

for (int i = 0; i < tms; i++)
for (int j = 0; j < tms; j++) {
matrix[i][j] = a[i][j] + b[i][j];
}

return matrix;
}

// --------------------------------------------------------------
public float[][] MultiplyMatrix(float[][] a, float[][] b) throws Exception {

if(a[0].length != b.length)
throw new Exception("Matrices incompatible for multiplication");
float matrix[][] = new float[a.length][b[0].length];

for (int i = 0; i < a.length; i++)
for (int j = 0; j < b[i].length; j++)
matrix[i][j] = 0;

//cycle through answer matrix
for(int i = 0; i < matrix.length; i++){
for(int j = 0; j < matrix[i].length; j++){
matrix[i][j] = calculateRowColumnProduct(a,i,b,j);
}
}
return matrix;
}

public float calculateRowColumnProduct(float[][] A, int row, float[][] B, int col){
float product = 0;
for(int i = 0; i < A[row].length; i++)
product +=A[row][i]*B[i][col];
return product;
}
// --------------------------------------------------------------

public float[][] Transpose(float[][] a) {
if (INFO) {
System.out.println("Performing Transpose...");
}

float m[][] = new float[a[0].length][a.length];

for (int i = 0; i < a.length; i++)
for (int j = 0; j < a[i].length; j++)
m[j][i] = a[i][j];
return m;
}

// --------------------------------------------------------------

public float[][] Inverse(float[][] a) throws Exception {
// Formula used to Calculate Inverse:
// inv(A) = 1/det(A) * adj(A)
if (INFO) {
System.out.println("Performing Inverse...");
}
int tms = a.length;

float m[][] = new float[tms][tms];
float mm[][] = Adjoint(a);

float det = Determinant(a);
float dd = 0;

if (det == 0) {
statusBar.setText("Determinant = 0, Not Invertible.");
if (INFO) {
System.out.println("Determinant = 0, Not Invertible.");
}
} else {
dd = 1 / det;
}

for (int i = 0; i < tms; i++)
for (int j = 0; j < tms; j++) {
m[i][j] = dd * mm[i][j];
}

return m;
}

// --------------------------------------------------------------

public float[][] Adjoint(float[][] a) throws Exception {
if (INFO) {
System.out.println("Performing Adjoint...");
}
int tms = a.length;

float m[][] = new float[tms][tms];

int ii, jj, ia, ja;
float det;

for (int i = 0; i < tms; i++)
for (int j = 0; j < tms; j++) {
ia = ja = 0;

float ap[][] = new float[tms - 1][tms - 1];

for (ii = 0; ii < tms; ii++) {
for (jj = 0; jj < tms; jj++) {

if ((ii != i) && (jj != j)) {
ap[ia][ja] = a[ii][jj];
ja++;
}

}
if ((ii != i) && (jj != j)) {
ia++;
}
ja = 0;
}

det = Determinant(ap);
m[i][j] = (float) Math.pow(-1, i + j) * det;
}

m = Transpose(m);

return m;
}

// --------------------------------------------------------------

public float[][] UpperTriangle(float[][] m) {
if (INFO) {
System.out.println("Converting to Upper Triangle...");
}

float f1 = 0;
float temp = 0;
int tms = m.length; // get This Matrix Size (could be smaller than
// global)
int v = 1;

iDF = 1;

for (int col = 0; col < tms - 1; col++) {
for (int row = col + 1; row < tms; row++) {
v = 1;

outahere: while (m[col][col] == 0) // check if 0 in diagonal
{ // if so switch until not
if (col + v >= tms) // check if switched all rows


{

iDF = 0;

break outahere;

} else {

for (int c = 0; c < tms; c++) {
temp = m[col][c];
m[col][c] = m[col + v][c]; // switch rows
m[col + v][c] = temp;
}
v++; // count row switchs
iDF = iDF * -1; // each switch changes determinant
// factor
}
}

if (m[col][col] != 0) {
if (DEBUG) {
System.out.println("tms = " + tms + " col = " + col
+ " row = " + row);
}

try {
f1 = (-1) * m[row][col] / m[col][col];
for (int i = col; i < tms; i++) {
m[row][i] = f1 * m[col][i] + m[row][i];
}
} catch (Exception e) {
System.out.println("Still Here!!!");
}

}

}
}

return m;
}

// --------------------------------------------------------------

public float Determinant(float[][] matrix) {
if (INFO) {
System.out.println("Getting Determinant...");
}
int tms = matrix.length;

float det = 1;

matrix = UpperTriangle(matrix);

for (int i = 0; i < tms; i++) {
det = det * matrix[i][i];
} // multiply down diagonal

det = det * iDF; // adjust w/ determinant factor

if (INFO) {
System.out.println("Determinant: " + det);
}
return det;
}

}