MySQL Alter Constraints

Alter Constraint 

A constraint allows columns in a table that restrict how many types of changeable data values are in a column of the table.

PRIMARY KEY Constraint : In the primary key constraints, there are some different things such as given below.

• Each record uniquely identifies in primary key constraint in a database table
• The Primary keys must contain unique values 
• NULL values not allow in a primary key columns
• Each table should have a primary key, and can have only ONE primary key

PRIMARY KEY Constraint on CREATE TABLE :

The following commands creates a PRIMARY KEY on the "P_Id" column when the "Persons" table is created.

For Example :

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);

PRIMARY KEY Constraint on ALTER TABLE :

First we have to create a PRIMARY KEY constraint on the "P_Id" column when the table is already created.
Syntax :

mysql> ALTER TABLE Persons
       >ADD PRIMARY KEY (P_Id);

To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns.

Syntax:

mysql> ALTER TABLE Persons
       > ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName) ;

To DROP a PRIMARY KEY Constraint :

When we have to drop a PRIMARY KEY constraint then we use the following commands:

Syntax :

mysql> ALTER TABLE Persons
       >  DROP PRIMARY KEY;

UNIQUE Constraint :

In the UNIQUE constraint, each record is uniquely identifies in a database table.  The UNIQUE KEY and PRIMARY KEY constraints both support a appoint for differentness  for a column or set of columns.Automatically a PRIMARY KEY constraint  has a UNIQUE constraint defined on it.

 UNIQUE Constraint on CREATE TABLE :

The following command creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is already created:

Syntax :

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
);

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following command .

Syntax :

CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
);

UNIQUE Constraint on ALTER TABLE :

To create a UNIQUE constraint on the "P_Id" column when the table is already created, use the following command:

Syntax :

ALTER TABLE Persons
ADD UNIQUE (P_Id);

To allow naming of a UNIQUE constraint, and for defining a UNIQUE constraint on multiple columns, use the following command :

Syntax:

ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)

To DROP a UNIQUE Constraint :

To drop a UNIQUE constraint use the following Syntax .

Syntax :

mysql> ALTER TABLE Persons
       > DROP INDEX uc_PersonID ;

Creating a foreign key constraint :

 The individual  purpose of a foreign key constraint is to define a relationship between two tables.

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

Example :

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

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

FOREIGN KEY Constraint on CREATE TABLE :

The following command creates a FOREIGN KEY on the "P_Id" column when the "Orders" table is already created:

Syntax:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns.

Syntax:

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
);

FOREIGN KEY Constraint on ALTER TABLE :

To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following commands.

Syntax:

mysql>ALTER TABLE Orders
       >ADD FOREIGN KEY (P_Id)
      > REFERENCES Persons(P_Id) ;

To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns.
Syntax:

mysql>ALTER TABLE Orders
       >ADD CONSTRAINT fk_PerOrders
       >FOREIGN KEY (P_Id)
       >REFERENCES Persons(P_Id);

To DROP a FOREIGN KEY Constraint :

When we have to drop a FOREIGN KEY constraint, use the following SQL syntax :

Syntax :

mysql>  ALTER TABLE Orders
       > DROP FOREIGN KEY fk_PerOrders ;