CHICHILI GROUOPS

Monday, 2 November 2015

A Tag

Tomorrow is a fresh for all aged peoples no one can experienced ,Be ready either positive are negtive

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

<?xml version='1.0' encoding='UTF-8'?>
<!DOCTYPE hibernate-configuration PUBLIC
"-//Hibernate/Hibernate Configuration DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>
<session-factory>
<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver
</property>
<property name="connection.url">jdbc:oracle:thin:@www.java4s.com:1521:XE</property>
<property name="connection.username">system</property>
<property name="connection.password">admin</property>

<property name="dialect">org.hibernate.dialect.OracleDialect</property>
<property name="show_sql">true</property>
<property name="hbm2ddl.auto">update</property>

<mapping resource="Product.hbm.xml"></mapping>
</session-factory>
</hibernate-configuration>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

<?xml version='1.0' encoding='UTF-8'?>

<!DOCTYPE hibernate-configuration PUBLIC

"-//Hibernate/Hibernate Configuration DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-configuration-3.0.dtd">

<hibernate-configuration>

<session-factory>

<property name="connection.driver_class">oracle.jdbc.driver.OracleDriver

</property>

<property name="connection.url">jdbc:oracle:thin:@www.java4s.com:1521:XE</property>

<property name="connection.username">system</property>

<property name="connection.password">admin</property>

<property name="dialect">org.hibernate.dialect.OracleDialect</property>

<property name="show_sql">true</property>

<property name="hbm2ddl.auto">update</property>

<mapping resource="Product.hbm.xml"></mapping>

</session-factory>

</hibernate-configuration>

Product.hbm.xml

<?xml version="1.0"?>
<!DOCTYPE hibernate-mapping PUBLIC
"-//Hibernate/Hibernate Mapping DTD 3.0//EN"
"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>
<class name="str.Product" table="products">

<composite-id>
<key-property name="productId" column="pid"  />
<key-property name="proName" column="pname" length="10" />
</composite-id>

<property name="price"/>

</class>
</hibernate-mapping>

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

<?xml version="1.0"?>

<!DOCTYPE hibernate-mapping PUBLIC

"-//Hibernate/Hibernate Mapping DTD 3.0//EN"

"http://hibernate.sourceforge.net/hibernate-mapping-3.0.dtd">

<hibernate-mapping>

<class name="str.Product" table="products">

<composite-id>

<key-property name="productId" column="pid" />

<key-property name="proName" column="pname" length="10" />

</composite-id>

<property name="price"/>

</class>

</hibernate-mapping>

ForOurLogic.java

package str;

import org.hibernate.*;
import org.hibernate.cfg.*;

public class ForOurLogic { 

 public static void main(String[] args)
 {

  Configuration cfg = new Configuration();
  cfg.configure("hibernate.cfg.xml"); 

  SessionFactory factory = cfg.buildSessionFactory();
  Session session = factory.openSession();

  Product p=new Product();

  p.setProductId(101);
                p.setProName("iPhone");
                p.setPrice(25000); 

        Transaction tx=session.beginTransaction();
           session.save(p);
           System.out.println("Object Loaded successfully.....!!");
        tx.commit();     

  session.close();
  factory.close();
 }

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

package str;

import org.hibernate.*;

import org.hibernate.cfg.*;

public class ForOurLogic {

public static void main(String[] args)

{

Configuration cfg = new Configuration();

cfg.configure("hibernate.cfg.xml");

SessionFactory factory = cfg.buildSessionFactory();

Session session = factory.openSession();

Product p=new Product();

p.setProductId(101);

p.setProName("iPhone");

p.setPrice(25000);

Transaction tx=session.beginTransaction();

session.save(p);

System.out.println("Object Loaded successfully.....!!");

tx.commit();

session.close();

factory.close();

}

}

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)

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

Subscribe to: Posts (Atom)