MYSQL KEY..
1. Primary key
2. Unique key
3. Foreign key
4. Composite key
1. Primary key :
- primary key is a single or combination of the field, which is used to identify each record in a table uniquely.
- The primary key column value must be unique.
- Each table can contain only one primary key.
- The primary key column cannot be null or empty.
- It is recommended to use INT or BIGINT data type for the primary key column.
Ex : If we want to create only one primary key column into the table, use the below syntax:
CREATE TABLE table_name(
col1 datatype PRIMARY KEY,
col2 datatype,
);
- Primary Key Using ALTER TABLE Statement
1.Syntax of the ALTER TABLE statement to create a primary key in MySQL:
ALTER TABLE table_name ADD PRIMARY KEY(column_list);
Ex :
ALTER TABLE Persons ADD PRIMARY KEY(Person_ID);
2. DROP Primary Key
Syntax : ALTER TABLE table_name DROP PRIMARY KEY;
Ex :
ALTER TABLE Persons DROP PRIMARY KEY;
2. Unique key
- A unique key in MySQL is a single field or combination of fields that ensure all values going to store into the column will be unique.
- unique constraint is like a primary key but in one table there can be multiple unique constraint .
- due to unique constraint column cannot stores duplicate values.
- MySQL allows us to use more than one column with UNIQUE constraint in a table.
- It can accept a null value, but MySQL allowed only one null value per column.
Synatax :
CREATE TABLE table_name(
col1 datatype,
col2 datatype UNIQUE,
...
);
- unique key on multiple columns
CREATE TABLE Student3 (
Stud_ID int,
Roll_No int,
Name varchar(45) NOT NULL,
Email varchar(45),
Age int,
City varchar(25),
CONSTRAINT uc_rollno_email Unique(Roll_No, Email)
);
Here , unique key value contains two columns that are Roll_No and Email.
- DROP Unique Key
Ex :
ALTER TABLE Student3 DROP INDEX uc_rollno_email;
-ADD a unique key to the column of an existing table
Ex :
ALTER TABLE Student3 ADD CONSTRAINT uc_rollno_email UNIQUE(Roll_No, Email);
3. Foreign key
- The foreign key is used to link one or more than one table together.
- A foreign key matches the primary key field of another table. It means a foreign key field in one table refers to the primary key field of the other table.
- To learn concept of foreign key we need to create two tables named "customer" and "contact" using the below statement:
- CASCADE: It is used when we delete or update any row from the parent table, the values of the matching rows in the child table will be deleted or updated automatically.
Table 1: customer
CREATE TABLE customer (
ID INT NOT NULL AUTO_INCREMENT,
Name varchar(50) NOT NULL,
City varchar(50) NOT NULL,
PRIMARY KEY (ID)
);
Table 2: contact
CREATE TABLE contact (
ID INT,
Customer_Id INT,
Customer_Info varchar(50) NOT NULL,
INDEX par_ind (Customer_Id),
CONSTRAINT fk_customer FOREIGN KEY (Customer_Id)
REFERENCES customer(ID)
ON DELETE CASCADE
ON UPDATE CASCADE
);
after inserting record if there are same data in parent & child class then we can delete & upadate it .
we are going to delete the referential data that removes records from both tables. We have defined the foreign key in the contact table as:
FOREIGN KEY (Customer_Id) REFERENCES customer(ID)
ON DELETE CASCADE
ON UPDATE CASCADE.
- It means if we delete any customer record from the customer table, then the related records in the contact table should also be deleted.
- And the ON UPDATE CASCADE will updates automatically on the parent table to referenced fields in the child table(Here, it is Customer_Id).
- Execute this statement that deletes a record from the table whose name is JOHN(john prensent is both delete).
DELETE FROM customer WHERE Name='John';
ON UPDATE CASCADE. Here, we are going to update the Customer_Id of Mary in the contact table as:
mysql> UPDATE customer SET id=2 WHERE Name='Mary';
4. Composite key
- A composite key in MySQL is a combination of two or more than two columns in a table that allows us to identify each row of the table uniquely.
- Any key such as primary key, super key, or candidate key can be called composite key when they have combined with more than one attribute.
- A composite key is useful when the table needs to identify each record with more than one attribute uniquely.
- A column used in the composite key can have different data types. Thus, it is not required to be the same data type for the columns to make a composite key in MySQL.
Ex :
CREATE TABLE Product (
Prod_ID int NOT NULL,
Name varchar(45),
Manufacturer varchar(45),
PRIMARY KEY(Name, Manufacturer)
);
we need to insert the values into this table as given below:
INSERT INTO Product (Prod_ID, Name, Manufacturer)
VALUES (101, 'Soap', 'Hamam'),
(102, 'Shampoo', 'Teresme'),
(103, 'Oil', 'Daber Almond');
Again execute the below insert statement to understand composite key more clearly:
INSERT INTO Product (Prod_ID, Name, Manufacturer)
VALUES (101, 'Soap', 'Hamam'); // Here Haman already exist so its duplicate
INSERT INTO Product (Prod_ID, Name, Manufacturer)
VALUES (101, 'Soap', 'LUX'); // LUX is not already exist so it will insert into table.
we can see that if we try to add the combination of the same product name and manufacturer, then it will throw an error saying that: Duplicate entry for product.primary.