DATABASE KEYS- primary, unique,foregin

 Ø DATABASE KEYS:- Keys are the special type of columns which are used to manage the complete data records.

Ø Lets us see the types of keys

1. Primary key.

2. Unique key.

3. Foreign key .

 

1. Primary key :-

l The record should not be null.

l The record should not repeated.

Ex: CREATE TABLE KEYDEMO (ROLL INT  PRIMARY KEY , NAME VARCHAR(50));

INSERT INTO KEYDEMO (NAME) VALUES ('RAISONI');

 

Q. HOW TO CREATE THE PRIMARY KEY USING THE ALTER COMMAND ?

ANS:- CREATE TABLE KEYDB2 (ROLL INT , NAME VARCHAR (50));// HOW TO ADD NOT NULL IN THIS

ALTER  TABLE KEYDB2 ALTER COLUMN ROLL INT NOT NULL;// CONVERTING THE ROLL COLUMN TO NOTNULL.

ALTER TABLE KEYDB2 ADD PRIMARY KEY (ROLL);// NOW CONVERTING THE ROLL COLUMN IN THE PRIMARY KEY

INSERT INTO KEYDB2 VALUES( 1,'RAJU');// INSERT THE RECORD AND CHECKING IT WHETHER IT IS PRIMARY OR NOT.

Ø UNIQUE KEY:-

1. The data should not repeated.

2. The records can be blank / The data can be filled null.

Ex:-CREATE TABLE  UDB1 (ROLL INT UNIQUE  , NAME VARCHAR (50));

INSERT INTO UDB1 VALUES(101,'BABU BHAIYA');

INSERT INTO UDB1 (NAME) VALUES('BABU BHAIYA');

 

 

Q.HOW TO CREATE UNIQUE KEY USING THE ALTER COMMAND?

ANS : CREATE TABLE UDB2 (ROLL INT , NAME VARCHAR (50));// CREATING NORMAL TABLE

ALTER TABLE UDB2  ADD UNIQUE (ROLL);// MAKING THE ROLLCOLUMN UNIQUE

INSERT INTO UDB2 (NAME)VALUES('KAKA JI');// INSERTING DATA AND RUNNING THE QUERY 2 TIME TO CHECH WHETHER THE DATA IS UNQIUE OR NOT

SELECT * FROM UDB2

 

l FOREIGN KEY :-

Foreign key is a column that establish the relationship between 2 tables it is also called REFERENTIAL KEY.

l Property of foreign key:- 

1. Master table uses primary key .

2. Sub table uses not null column.

EX:-CREATE TABLE MAINBD (ID INT PRIMARY KEY , NAME VARCHAR (50));

INSERT INTO MAINBD VALUES (101,'RAJA'),(102,'RANI'),(103,'CHOR'),(104,'POLICE'),(105,'NAUKAR');

 

 

R. HOW TO CREATE FOREIGN KEY?

ANS:-

IT IS ONLY CREATED IN SUB TABLE SYNTAX:

 FOREIGN KEY (COLUMNNAME) REFERENCES TABLENAME(COLUMNNAME).

 

EX:-

 

CREATE TABLE SUBBD(ID INT NOT NULL ,MARK INT ,FOREIGN KEY (ID) REFERENCES MAINBD (ID));

INSERT INTO SUBBD VALUES (109,200);

SELECT * FROM SUBBD;

\

 

 

Task 2 of SQL :-