How To Define Constraints In MySQL Table

Now days normally table must be have foreign key and primary key relation, So when you defining the foreign key in a table you should also define foreign Key constraint on child table.
With help of Constraint the user cannot delete parent data unless he will not delete all dependent parent data from child table. InnoDb support foreign keys Constraints.

It type of security which is apply on table level in database.
Below code is used to create a table and define FOREIGN KEY with FOREIGN KEY Constraint.

Rules to define constrains:

1. The PK column(s) in the parent table and the FK column(s) must be the same data type.
2. The PK column(s) in the parent table and the FK column(s), if they have a define collation type, must have the same collation type;
3. The two tables must have the same charset.
4. The two tables must be ENGINE=InnoDB.
5. If there is data already in the foreign key table, the FK column value(s) must match values in the parent table PK columns.

Today I will described in simple example how to create table with FOREIGN KEY Constraints.

Syntax:

FOREIGN KEY (parentId) REFERENCES parent(parentId)

Example:
[code type=php”]
CREATE TABLE IF NOT EXISTS operationdatafield (
DataFieldId int(11) NOT NULL AUTO_INCREMENT,
OperationId int(11) NOT NULL,
FieldName varchar(255) NOT NULL,
CreatedBy varchar(50) NOT NULL,
CreatedOn datetime NOT NULL,
ModifiedBy varchar(50) NOT NULL,
ModifiedOn datetime NOT NULL,
DependentField varchar(50) NOT NULL,
PRIMARY KEY (DataFieldId),
FOREIGN KEY (OperationId) REFERENCES Operation(OperationId)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;