Monday, 18 November 2013

Example of composite primary key in hibernate?
Composite primary keys means having more than one primary key, let us see few points on this concept
  • If the table has a primary key then in the hibernate mapping file we need to configure that column by using <id /> element right..!
  • Even though the database table doesn’t have any primary key, we must configure one column as id (one primary key is must)
  • If the database table has more than one column as primary key then we call it as composite primary key, so if the table has multiple primary key columns , in order to configure these primary key columns in the hibernate mapping file we need to use one new element called <composite-id …..> </composite-id>
  • Files required….
  • Product.java (Pojo)
  • ForOurLogic.java (for our logic)
  • hibernate.cfg.xml
  • Product.hbm.xml
  •  
  • public class Product implements java.io.Serializable{
     
        private static final long serialVersionUID = 1L;
     
        private int productId;
        private String proName;
        private double price;
     
        public void setProductId(int productId)
        {
            this.productId = productId;
        }
        public int getProductId()
        {
            return productId;
        }
     
        public void setProName(String proName)
        {
            this.proName = proName;
        }
        public String getProName()
        {
            return proName;
        }
     
        public void setPrice(double price)
        {
            this.price = price;
        }
        public double getPrice()
        {
            return price;
        }
    }

    hibernate.cfg.xml


    Product.hbm.xml


    ForOurLogic.java

    Eclipse output

    In the database
    Notes:
  • see Product.java pojo class, in line number 3 i have implemented the java.io.Serializable interface,  this is the first time am writing this implementation for the pojo class right…!  we will see the reason why we use this serializable interface later.
  • But remember, if we want to use the composite primary keys we must implement our pojo class with Serializable interface
  • hibernate.cfg.xml is normal as previous programs, something like hello world program
  • come to Product.hbm.xml, see line number 9-12, this time we are using one new element<composite-id>
  • Actually if we have a single primary key, we need to use <id> element, but this time we have multiple primary keys, so we need to use this new element <composite-id>
  • Actually we will see the exact concept of this composite primary keys in the next example (loading an object with composite key)
  •  
Posted by at No comments:

Saturday, 16 November 2013

Examples of SQL.Easy way to understand

CREATE TABLE EMPLOYEE(SNO INT,NAME VARCHAR(90),AGE INT,SALARY INT);
INSERT INTO EMPLOYEE VALUES(1,'ABC',22,12000);
INSERT INTO EMPLOYEE VALUES(2,'XYZ',23,8000);
INSERT INTO EMPLOYEE VALUES(3,'RAMANA',30,10000);
INSERT INTO EMPLOYEE VALUES(4,'RAMA',18,15000);
INSERT INTO EMPLOYEE VALUES(5,'VIJAY',15,20000);

SELECT COUNT(*)FROM EMPLOYEE;
(it count all no.of  vlues)
SELECT DISTINCT(SNO)FROM EMPLOYEE;(it print total   sno)
1
2
4
5
3
  SELECT DISTINCT(NAME)FROM EMPLOYEE;(it print all names of table)
RAMA
VIJAY
ABC
RAMANA
XYZ
SELECT DISTINCT(AGE)FROM EMPLOYEE;
22
30
23
18
15
SELECT COUNT(sno)FROM EMPLOYEE;(it count how many sno)
5
SELECT COUNT(Name)FROM EMPLOYEE; ;(it count how many names)
5
SELECT sum(salary)FROM EMPLOYEE;
12000
8000
10000
15000
20000
TOTAL =65000(O/p)
SELECT MIN(salary)FROM EMPLOYEE;
80000
SELECT MAX(salary)FROM EMPLOYEE;
20000
SELECT NAME,SALARY FROM EMPLOYEE WHERE SALARY=(SELECT MIN(SALARY)FROM EMPLOYEE);
XYZ         8000(IT PRODUSE min salary with name)
SELECT NAME,SALARY FROM EMPLOYEE WHERE SALARY=(SELECT Max(SALARY)FROM EMPLOYEE);
It produce name with max salary
VIJAY     20000
For  finding second  maximum value
select max(salary) from employee where salary<(select max(salary)from employee)
15000
select max(age) from employee where age<(select max(age)from employee)
23
For  finding second  minum   value
select min(age) from employee where age>(select min(age)from employee)
18
select min(salary) from employee where salary>(select min(salary)from employee)
10000
BETWEEN AND  NOT BETWEEN
BETWEEN
It shows between two values wat are present that will be print
Table
1              ABC        22           12000
2              XYZ         23           8000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
SELECT *FROM EMPLOYEE WHERE SALARY BETWEEN 10000 AND 20000;
1              ABC        22           12000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
Not between
It tells between two values whic are there that it did not print.remaning
1              ABC        22           12000
2              XYZ         23           8000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
SELECT *FROM EMPLOYEE WHERE SALARY not BETWEEN 10000 AND 20000;
2          XYZ      23        8000
IN
1              ABC        22           12000
2              XYZ         23           8000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
SELECT *FROM EMPLOYEE WHERE SNO IN(1,3,5);
1              ABC        22           12000
3              RAMANA             30           10000
5              VIJAY     15           20000
NOT IN
1              ABC        22           12000
2              XYZ         23           8000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
SELECT *FROM EMPLOYEE WHERE SNO NOT IN(1,3,5);
2              XYZ         23           8000
4              RAMA   18           15000
SELECT *FROM EMPLOYEE WHERE SNO IS NULL;
EMPTY
SELECT *FROM EMPLOYEE WHERE SNO IS NOT NULL;
1              ABC        22           12000
2              XYZ         23           8000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
LIKE,NOTLIKE
1              ABC        22           12000
2              XYZ         23           8000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
SELECT * FROM  EMPLOYEE WHERE NAME LIKE '%AM%';
WHERE AM is present that it will print
3              RAMANA             30           10000
4              RAMA   18           15000
SELECT * FROM  EMPLOYEE WHERE NAME LIKE 'RA%';
Starting with RA that it wll be present
3              RAMANA             30           10000
4              RAMA   18           15000
SELECT * FROM  EMPLOYEE WHERE NAME LIKE '%Z';
END WITH Z THAT IT WILL BE PRINT
2              XYZ         23           8000
SELECT * FROM  EMPLOYEE WHERE NAME  LIKE '_A%';
SECON LETTERS START WITH A  THAT IT WILL PRINT
SELECT * FROM  EMPLOYEE WHERE NAME  LIKE '%R_ _A%';
IT IS print between R and 2empty values A
3              RAMANA             30           10000
4              RAMA   18           15000
SELECT * FROM  EMPLOYEE WHERE NAME  LIKE '%A%';
WHERE  EVER A IS THERE THAT IT PRINT
1              ABC        22           12000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
NOT LIKE
1              ABC        22           12000
2              XYZ         23           8000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000
SELECT * FROM  EMPLOYEE WHERE NAME NOT LIKE '%R__A%';
1              ABC        22           12000
2              XYZ         23           8000
5              VIJAY     15           20000
SELECT * FROM  EMPLOYEE WHERE NAME NOT  LIKE '%A%';
WHERE EVER A IS  PRESENT THAT IT IS NOT PRINT.REMINING IT WILL BE PRINT
2              XYZ         23           8000
SELECT * FROM  EMPLOYEE WHERE NAME NOT  LIKE '_A%';
WHERE SECOND LETTER A IS  PRESENT THAT IT IS NOT PRINT.REMINING IT WILL BE PRINT
1              ABC        22           12000
2              XYZ         23           8000
5              VIJAY     15           20000
SELECT * FROM  EMPLOYEE WHERE NAME NOT  LIKE '%z';
WHERE IS FIRST LETTER “Z” IS  PRESENT THAT IT IS NOT PRINT.REMINING IT WILL BE PRINT
1              ABC        22           12000
2              XYZ         23           8000
3              RAMANA             30           10000
4              RAMA   18           15000
5              VIJAY     15           20000


CREATE TABLE TAB1(ID INT NOT NULL,NAME VARCHAR(90),AGE INT)
INSERT INTO TAB1 VALUES(1,'ABC',23);
 INSERT INTO TAB1 VALUES(2,'PETER',4);
 INSERT INTO TAB1 VALUES(2,'STANLEY',12);
 INSERT INTO TAB1 VALUES(3,'JAMES',23);
 INSERT INTO TAB1 VALUES(4,'PATIL',34);
 INSERT INTO TAB1 VALUES(5,'PRINCE',22);

1                                                                                                          ABC    23
2                                                                                                                      PETER           4
2                                                                                                                      STANLEY      12
3                                                                                                                      JAMES          23
4                                                                                                                      PATIL            34
5                                                                                                                      PRINCE        22
INSERT INTO TAB1 (ID,NAME)VALUES(5,'ADAM');
1                                                                                                                      ABC               23
2                                                                                                                      PETER           4
2                                                                                                                      STANLEY      12
3                                                                                                                      JAMES          23
4                                                                                                                      PATIL            34
5                                                                                                                      PRINCE        22
5                                                                                                                      ADAM          NULL
INSERT INTO TAB1(AGE,ID)VALUES(45,13);
1                                                                                                                      ABC               23
2                                                                                                                      PETER           4
2                                                                                                                      STANLEY      12
3                                                                                                                      JAMES          23
4                                                                                                                      PATIL            34
5                                                                                                                      PRINCE        22
5                                                                                                                      ADAM          NULL
13                                                                                                                    NULL             45 INSERT INTO TAB1(NAME,AGE)VALUES('MAERC',25);//ERROR “ID IS GIVING NOT NULL
INSERT INTO TAB1(NAME,AGE)VALUES('MAERC',25)
Error report:
SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."TAB1"."ID")
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:   
*Action:
UNIQUE CONSTRANT
CREATE TABLE TAB12(ID INT,NAME VARCHAR(90),AGE INT,CONSTRANT UK24 UNIQUE(ID)
 INSERT INTO TAB12 VALUES(1,'ABC',25);
 INSERt INTO TAB12 VALUES(2,'PETER',12);
 INSERT INTO TAB12 VALUES(4,'STANLEY',32);
 INSERT INTO TAB12 VALUES(6,'JAMES',45);
 INSERT INTO TAB12 VALUES(13,'PATIL',23);
 INSERT INTO TAB12 VALUES(9,'PRINCE',20);
INSERT INTO TAB12 VALUES(9,'PRINCE',20);//ERROR(UNIQUE KEY NOT ALLOWED DUPLICATES)
{INSERT INTO TAB12 VALUES(9,'PRINCE',20)
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.UK24) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.}
 INSERT INTO TAB12(NAME,AGE)VALUES('WILLIAM',21);
1              ABC              25
2              PETER                    12
4              STANLEY              32
6              JAMES  45
13           PATIL     23
9              PRINCE 20
NULL     WILLIAM              21
INSERT INTO TAB12(NAME)VALUES('RAMANA');
1              ABC        25
2              PETER    12
4              STANLEY              32
6              JAMES  45
13           PATIL     23
9              PRINCE 20
NULL     WILLIAM              21
NULL     RAMANA             NULL
INSERT INTO TAB12(ID)VALUES(21);
1              ABC        25
2              PETER    12
4              STANLEY              32
6              JAMES  45
13           PATIL     23
9              PRINCE 20
NULL     WILLIAM              21
NULL     RAMANA             NULL
21           NULL                   NULL
PRIMARYKEY CONSTRANT
CREATE TABLE TAB2(SNO INT,NAME VARCHAR(90),AGE INT,EMAIL VARCHAR(90),CONSTRAINT
PK24 PRIMARY KEY(NAME));
INSERT INTO  TAB2 VALUES(100,'STEPHIN',56,'ABC@A.COM');
 INSERT INTO  TAB2 VALUES(101,'RAMA',66,'RAMA@A.COM');
  INSERT INTO  TAB2 VALUES(10,'SREE',34,'SREE@A.COM');
   INSERT INTO  TAB2 VALUES(102,'KHAN',27,'KHANC@A.COM');
100         STEPHIN               56           ABC@A.COM
101         RAMA   66           RAMA@A.COM
10           SREE      34           SREE@A.COM
102         KHAN    27           KHANC@A.COM  
INSERT INTO TAB2 (NAME,AGE)VALUES('MARC',3);
100         STEPHIN               56           ABC@A.COM
101         RAMA   66           RAMA@A.COM
10           SREE      34           SREE@A.COM
102         KHAN    27           KHANC@A.COM
NULL     MARC   3              NULL
INSERT INTO TAB2 (SNO,AGE)VALUES(1,23);
PK IS NAME ,SO PK  IS NOT NULL VALUS
INSERT INTO TAB2 (SNO,AGE)VALUES(1,23)
Error report:
SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."TAB2"."NAME")
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:   
*Action:
INSERT INTO TAB2 (SNO,EMAIL,NAME)VALUES(11,'RATANA@GMAIL.COM','RATANA');
100         STEPHIN               56           ABC@A.COM
101         RAMA   66           RAMA@A.COM
10           SREE      34           SREE@A.COM
102         KHAN    27           KHANC@A.COM
NULL     MARC   3              NULL
11           RATANA               NULL     RATANA@GMAIL.COM
INSERT INTO TAB2 (SNO,EMAIL,NAME)VALUES(29,'RATANA@65GMAIL.COM','ABDUL');
100         STEPHIN               56           ABC@A.COM
101         RAMA   66           RAMA@A.COM
10           SREE      34           SREE@A.COM
102         KHAN    27           KHANC@A.COM
NULL     MARC   3              NULL
11           RATANA NULL   RATANA@GMAIL.COM
29           ABDUL  NULL     RATANA@65GMAIL.COM
INSERT INTO TAB2 (SNO,EMAIL,NAME)VALUES(789,'M.@GMAIL.COM','ABDUL');//ERROR
NOT ALLOWED DUPLICTES KEY IN PK
INSERT INTO TAB2 (SNO,EMAIL,NAME)VALUES(789,'M.@GMAIL.COM','ABDUL')
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.PK24) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
PK IS NOT ALLOWING DUPLICATESAND NULL VALUES
UK IS not ALLOWING DUPLICATES AND  ALLOWING NULL VALUES
SINGLE TABLE MULTIPLE CONSTRAINTS
CREATE TABLE TAB3(SNO INT NOT NULL,NAME VARCHAR(90),AGE INT,EMAIL VARCHAR(90),CONSTRAINT UK20 UNIQUE(EMAIL),CONSTRAINT PK20 PRIMARY KEY(NAME));
INSERT INTO TAB3 VALUES(1,'ABC',13,'ABC@G.COM');
INSERT INTO TAB3 VALUES(2,'PATIL',22,'PATIL@Y.COM');
 INSERT INTO TAB3 VALUES(4,'SAHOO',31,'SAHOO16@G.COM');
 INSERT INTO TAB3 VALUES(5,'SCOTT',31,'SCO@R.COM');
1          ABC     13        ABC@G.COM
2          PATIL   22        PATIL@Y.COM
4          SAHOO            31        SAHOO16@G.COM
5          SCOTT 31        SCO@R.COM
INSERT INTO TAB3 VALUES(1,'PATEL',22,'PATEL@Y.COM');
1          ABC     13        ABC@G.COM
2          PATIL   22        PATIL@Y.COM
4          SAHOO            31        SAHOO16@G.COM
5          SCOTT 31        SCO@R.COM
1          PATEL  22        PATEL@Y.COM
INSERT INTO TAB3 VALUES(1,'MADHAN',22,'PATEL@Y.COM');//ERROR
EMAIL IS WE ARE GIVING UNIQUE SO,UK IS NOT ALLOWED DUPLICATES
INSERT INTO TAB3 VALUES(1,'MADHAN',22,'PATEL@Y.COM')
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.UK20) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
INSERT INTO TAB3 VALUES(4,'SAI',31,'SAIL@H.COM');
1          ABC     13        ABC@G.COM
2          PATIL   22        PATIL@Y.COM
4          SAHOO            31        SAHOO16@G.COM
5          SCOTT 31        SCO@R.COM
1          PATEL  22        PATEL@Y.COM
4          SAI       31        SAIL@H.COM
INSERT INTO TAB3 VALUES(5,'SCOTT',31,'SCOTT@R.COM');
EMAIL IS WE ARE GIVING UNIQUE SO,UK IS NOT ALLOWED DUPLICATES
INSERT INTO TAB3 VALUES(5,'SCOTT',31,'SCOTT@R.COM')
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.PK20) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
INSERT INTO TAB3 (SNO,NAME,EMAIL)VALUES(8,'RAJ','RAJ@K.COM');(NOTE WERE GIVING PK AS NAME UK AS EMAIL.TWO MUST AND SHOULD HAVE REMINIG OPTIONAL)
1          ABC     13        ABC@G.COM
2          PATIL   22        PATIL@Y.COM
4          SAHOO            31        SAHOO16@G.COM
5          SCOTT 31        SCO@R.COM
1          PATEL  22        PATEL@Y.COM
4          SAI       31        SAIL@H.COM
8          RAJ      NULL    RAJ@K.COM
INSERT INTO TAB3 (SNO,NAME,EMAIL)VALUES(9,'RAJ','RAJAN@K.COM');//ERROR
NAME IS PK .PK NOT ALLOWED DUPLICATES
INSERT INTO TAB3 (SNO,NAME,EMAIL)VALUES(9,'RAJ','RAJAN@K.COM')
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.PK20) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
INSERT INTO TAB3 (SNO,NAME,EMAIL)VALUES(9,'RAJA','RAJAN@K.COM');
1          ABC     13        ABC@G.COM
2          PATIL   22        PATIL@Y.COM
4          SAHOO            31        SAHOO16@G.COM
5          SCOTT 31        SCO@R.COM
1          PATEL  22        PATEL@Y.COM
4          SAI       31        SAIL@H.COM
8          RAJ      NULL    RAJ@K.COM
9          RAJA    NULL    RAJAN@K.COM
INSERT INTO TAB3 (SNO,NAME)VALUES(45,'KING');
EMAIL IS UK. UK IS ALLOWING NULL VALUES ONLY.NOT DUPLICATES VALUES
1          ABC     13        ABC@G.COM
2          PATIL   22        PATIL@Y.COM
4          SAHOO            31        SAHOO16@G.COM
5          SCOTT 31        SCO@R.COM
1          PATEL  22        PATEL@Y.COM
4          SAI       31        SAIL@H.COM
8          RAJ      NULL    RAJ@K.COM
9          RAJA    NULL    RAJAN@K.COM
45        KING    NULL    NULL
INSERT INTO TAB3 (SNO,EMAIL)VALUES(45,'KING@z.COM');\\ERROR
NAME  IS  PK  .PK IS NOT ALLOWING NULL VALUES
INSERT INTO TAB3 (SNO,EMAIL)VALUES(45,'KING@z.COM')
Error report:
SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."TAB3"."NAME")
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:   
*Action:
Two unique key and two primary key for
One table
CREATE TABLE TAB5(SNO INT,NAME VARCHAR(90),AGE INT,CONSTRAINT UK10 UNIQUE(SNO),CONSTRAINT UK11 UNIQUE(NAME));
INSERT INTO TAB5 VALUES(1,'ABC',23);
INSERT INTO TAB5 VALUES(2,'XYZ',25);
1          ABC     23
2          XYZ      25
CREATE TABLE TAB5(SNO INT,NAME VARCHAR(90),AGE INT,CONSTRAINT PK10 PRIMARY KEY(SNO),CONSTRAINT PK11 PRIMARY KEY(NAME);//ERROR
ONE TABLE ONLY ONE PRIMARY KEY.NOT ALLOWED MORE THAN ONE PRIMARY KEY
COMPOSITE UNIQUE KEY AND PRIMARY KEY
In composite pk and uk both i.e sno,name both are same value than only it find duplicates then only d.b did not take
or both are different
It did find duplicates .andd.b is accept
IT MEANS WE CAN SUPPLY TWO VALUES IN ONE PRIMARY KEY.
CREATE TABLE TAB5(SNO INT,NAME VARCHAR(90),AGE INT,CONSTRAINT PK10 PRIMARY KEY(SNO,NAME));
INSERT INTO TAB5 VALUES(1,'ABC',22);
1          ABC     22
INSERT INTO TAB5 VALUES(1,'ABC',52);//ERROR SNO,NAME BOTH ARE PRIMARY KEY
SO IT DID NOT ALLOWED DUPLICATES
INSERT INTO TAB5 VALUES(1,'ABC',52)
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.PK10) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
INSERT INTO TAB5 VALUES(2,'ABC',52);
2          ABC     52
1          ABC     22
CREATE TABLE TAB5(SNO INT,NAME VARCHAR(90),AGE INT,CONSTRAINT UK8 UNIQUE(SNO,NAME));
INSERT INTO TAB5 VALUES(1,'ABC',22);
1          ABC     22
INSERT INTO TAB5 VALUES(1,'ABC',552);//error
Uk allowing null values and not aliowing duplicates
INSERT INTO TAB5 VALUES(1,'ABC',552)
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.UK8) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
INSERT INTO TAB5 VALUES(2,'ABC',55);
2          ABC     55
1          ABC     52(here  sno numbere is different then only it accept)
INSERT INTO TAB5 VALUES(2,'ABC',48);//error
INSERT INTO TAB5 VALUES(2,'ABC',48)
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.UK8) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
INSERT INTO TAB5 VALUES(2,'madhan',5415);
1          ABC     52
2          ABC     55
2          madhan           5415
Not null with unique and primary key
CREATE TABLE TAB6(ID INT NOT NULL,NAME VARCHAR(90),AGE INT,CONSTRAINT UK23 UNIQUE(ID))
INSERT INTO TAB6 VALUES(1,'RAM',23);
INSERT INTO TAB6 VALUES(2,'RAMU',23);
INSERT INTO TAB6 VALUES(3,'RAMU',23);
INSERT INTO TAB6 VALUES(4,'KANE',23);
INSERT INTO TAB6 VALUES(5,'BARUD',23);
1          RAM    23
2          RAM    23
3          RAMU  23
4          KANE   23
5          BARUD            23
INSERT INTO TAB6 VALUES(1,'cOOK',45);//ERROR ID IS UNIQUE KEY
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.UK23) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
INSERT INTO TAB6(NAME,AGE)VALUES('ABC',25);//ERROR
WE ARE GIVING id as a not null .but we are giving null values so error
SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."TAB6"."ID")
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:   
*Action:
INSERT INTO TAB6(ID)VALUES(6);
1          RAM    23
2          RAM    23
3          RAMU  23
4          KANE   23
5          BARUD            23
6          NULL NULL
(UNIQUE KEY Allowing   null values   )
INSERT INTO TAB6(ID,name)VALUES(7,'babu');
1          RAM    23
2          RAM    23
3          RAMU  23
4          KANE   23
5          BARUD            23
6          null   null        
7          babu    null
INSERT INTO TAB6(ID,name)VALUES(7,'king');
Id is not null.it not allowed duplicate id values
SQL Error: ORA-00001: unique constraint (SYSTEM.UK23) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.

CREATE TABLE TAB7(ID INT NOT NULL,NAME VARCHAR(90),AGE INT,CONSTRAINT PK23 PRIMARY KEY(ID));
INSERT INTO TAB7 VALUES (1,'ABC',24);
  INSERT INTO TAB7 VALUES (2,'XYZ',24);
   INSERT INTO TAB7 VALUES (4,'MADHAN',24);
    INSERT INTO TAB7 VALUES (3,'KING',24);
1          ABC     24
2          XYZ      24
4          MADHAN         24
3          KING    24
    INSERT INTO TAB7 VALUES (2,'BOBY',27);
ID IS GIVEN NOT NULL
Error report:
SQL Error: ORA-00001: unique constraint (SYSTEM.PK23) violated
00001. 00000 -  "unique constraint (%s.%s) violated"
*Cause:    An UPDATE or INSERT statement attempted to insert a duplicate key.
           For Trusted Oracle configured in DBMS MAC mode, you may see
           this message if a duplicate entry exists at a different level.
*Action:   Either remove the unique restriction or do not insert the key.
    INSERT INTO TAB7(ID,NAME) VALUES (6,'BOBY');
1          ABC     24
2          XYZ      24
4          MADHAN         24
3          KING    24
6          BOBY   NULL
    INSERT INTO TAB7(ID) VALUES (7);
1          ABC     24
2          XYZ      24
4          MADHAN         24
3          KING    24
6          BOBY  
7          NULL NULL
(WERE  givig id ,as a  pk and not null so above is possible    )
    INSERT INTO TAB7(ID,NAME,AGE) VALUES (8,'CHICHILI',19);
1          ABC     24
2          XYZ      24
4          MADHAN         24
3          KING    24
6          BOBY   NULL
7          NULL    NULL
8          CHICHILI          19
    INSERT INTO TAB7(ID,AGE) VALUES (9,19);
1          ABC     24
2          XYZ      24
4          MADHAN         24
3          KING    24
6          BOBY   NULL
7          NULL    NULL
8          CHICHILI          19
9          NULL    19
    INSERT INTO TAB7(NAME,AGE) VALUES ('INDIA',569);//ERRORcannot insert NULL into
Error report:
SQL Error: ORA-01400: cannot insert NULL into ("SYSTEM"."TAB7"."ID")
01400. 00000 -  "cannot insert NULL into (%s)"
*Cause:   
*Action:
OPERATION ON CONSTRANT
CREATE TABLE TAB8(SNO INT,NAME VARCHAR(90),SALARY INT);
DESC tab8;
DESC tab8
Name   Null Type        
------ ---- ------------
SNO         NUMBER(38)  
NAME        VARCHAR2(90)
SALARY      NUMBER(38)  
ALTER TABLE TAB8 ADD CONSTRAINT UK30 UNIQUE(SNO);
table TAB8 altered.
ALTER TABLE TAB8 DISABLE CONSTRAINT UK30 ;
table TAB8 altered.
ALTER TABLE TAB8 ENABLE CONSTRAINT UK30 ;
ALTER TABLE TAB8 DROP CONSTRAINT UK30 ;
ALTER TABLE TAB8 ADD CONSTRAINT PK30 PRIMARY KEY(SALARY) ;
DESC TAB8
table TAB8 created.
DESC tab8
Name   Null Type        
------ ---- ------------
SNO         NUMBER(38)  
NAME        VARCHAR2(90)
SALARY      NUMBER(38)  

table TAB8 altered.
DESC TAB8
Name   Null Type        
------ ---- ------------
SNO         NUMBER(38)  
NAME        VARCHAR2(90)
SALARY      NUMBER(38)  

Error starting at line 1 in command:
ALTER TABLE TAB8 DISABLE CONSTRAINT UK30 UNIQUE(SNO)
Error report:
SQL Error: ORA-00933: SQL command not properly ended
00933. 00000 -  "SQL command not properly ended"
*Cause:   
*Action:
table TAB8 altered.
desc tab8
Name   Null Type        
------ ---- ------------
SNO         NUMBER(38)  
NAME        VARCHAR2(90)
SALARY      NUMBER(38)  

table TAB8 altered.
desc tab8
Name   Null Type        
------ ---- ------------
SNO         NUMBER(38)  
NAME        VARCHAR2(90)
SALARY      NUMBER(38)  

table TAB8 altered.
table TAB8 altered.
ALTER TABLE TAB8 ADD CONSTRAINT PK30 PRIMARY KEY(SALARY) ;
DESC TAB8
Name   Null     Type        
------ -------- ------------
SNO             NUMBER(38)  
NAME            VARCHAR2(90)
SALARY NOT NULL NUMBER(38)  
ALTER TABLE TAB8 DISABLE CONSTRAINT PK30 ;
DESC TAB8;
Name   Null Type        
------ ---- ------------
SNO         NUMBER(38)  
NAME        VARCHAR2(90)
SALARY      NUMBER(38)  
FOREIGN KEY CONSTRAINT(FOR UNIQUE,PRIMARY KEY)
CREATE TABLE PERSON(ID INT,NAME VARCHAR(90),AGE INT,CONSTRAINT UK90 UNIQUE(ID));
DESC PERSON;
DESC PERSON
Name Null Type        
---- ---- ------------
ID        NUMBER(38)  
NAME      VARCHAR2(90)
AGE       NUMBER(38)  
CREATE TABLE ADDRESS(ID INT,HOUSE_NO VARCHAR(90),STR VARCHAR(90),CONSTRAINT FK10 FOREIGN KEY(ID)REFERENCES PERSON (ID));
DESC ADDRESS;
Name     Null Type        
-------- ---- ------------
ID            NUMBER(38)  
HOUSE_NO      VARCHAR2(90)
STR           VARCHAR2(90)
INSERT INTO PERSON VALUES(100,'ABC',22);
INSERT INTO PERSON VALUES(200,'XYZ',24);
100      ABC     22
200      XYZ      24
INSERT INTO ADDRESS VALUES(100,'123/9','BTM');
100      123/9   BTM
INSERT INTO ADDRESS VALUES(200,'500/2','JP NAGAR');
100      123/9   BTM
200      500/2   JP NAGAR
INSERT INTO ADDRESS VALUES(300,'30/2','KBS');
Error report:
SQL Error: ORA-02291: integrity constraint (SYSTEM.FK10) violated - parent key not found
02291. 00000 - "integrity constraint (%s.%s) violated - parent key not found"
*Cause:    A foreign key value has no matching primary key value.
*Action:   Delete the foreign key or add a matching primary key.

WE FOLLOW SOME RULE
FIRST INSERT ADDRESS AND SECOND INSERT PERSON TABLE.
FIRST DELETE ADDRESS TABLE AND SECOND DELETE PERSON TABLE
SQLJOIN

Posted by at No comments:
Subscribe to: Posts (Atom)