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 ;
