What is Constraint?

Constraint is the set of ristriction upon table column like not be null data set and not be duplicate contion to checks etc.

Primary Constraint

Primary key is master key that make table to MASTER TABLE. If any field have primary so it mean that value not be set NULL and DUPLICATE.

Syntax :-

alter_add_query Constraint key_name Primary key(primary_column);

Example

mysql> CREATE Table t1(
	-> id int primary key
	-> );
Query OK, 0 rows affected (0.06 sec)

mysql> CREATE Table t2(
	-> id int,
	-> Constraint pk Primary Key(id)
	-> );
Query OK, 0 rows affected (0.13 sec)

mysql> ALTER Table t3 ADD Constraint pk Primary key(primary_column);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

Foreign Constraint

Foreign key is set table as Secondary Table it exmple like mark table of student. It is check column data always insert that present in that primary field.

Syntax :-

alter_add_query Constraint Foreign Key(foriegn_column) REFERENCES table_name(primary_column);

Example

mysql> CREATE TABLE t4(
	-> id int references t1(id)
	-> );
Query OK, 0 rows affected (0.05 sec)

mysql> CREATE TABLE t5(
	-> id int,
	-> Constraint fk Foreign key(id) references t2(id)
	-> );
Query OK, 0 rows affected (0.07 sec)

mysql>

---

Unique Constraint

The unique constraint is set column have not be store duplicate data. if you can select unique data from table you can use distinct.

Syntax :-

alter_add_query Column column_name datatype UNIQUE;

Example

mysql> CREATE Table rollno(
	-> rollno int unique
	-> );
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER Table bill ADD Column bill_id int UNIQUE;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

---

NOt NULL Constraint

The NOT NULL Constraint is set column data have be not null if you can entity new row. With deal to not null you can use is Operator.

Syntax :-

alter_add_query Column column_name datatype NOT NULL;

Example

mysql> CREATE Table personal(
-> fname char(30) NOT NULL
-> );
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER Table personal ADD Column lname char(30) NOT NULL;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

---

NULL Constraint

The NULL Constraint is set you can insert it data is null (void). With deal to null you can use is Operator.

Syntax :-

alter_add_query Column column_name datatype NULL;

Example

mysql> CREATE Table personal(
-> fname char(30) NULL
-> );
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER Table personal ADD Column lname char(30) NULL;
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

---

CHECK Constraint

The Check Constraint is used for set condition inside the column.

Syntax :-

alter_add_query Column column_name datatype CHECK(condition);

Example

mysql> CREATE Table sex(
-> gender char(1) CHECK(in('T','M','F'))
-> );
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER Table personal ADD Column m1 int CHECK(m1 > 100);
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>

---

Default Constraint

The default Constraint is use to set default value of the column like if user not enter last_name it set default NULL.

Syntax :-

alter_add_query Column column_name datatype DEFAULT value;

Example

mysql> CREATE Table departement(
-> student_Class char('40') DEFAULT 'BCA'
-> );
Query OK, 0 rows affected (0.07 sec)

mysql> ALTER Table personal ADD Column status char(30) default 'bachlor';
Query OK, 0 rows affected (0.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql>