1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

how constraints used in oracle

Discussion in 'SQL PL/SQL' started by krishna84, Apr 16, 2013.

  1. krishna84

    krishna84 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    hyderabad
    Hai this material is very used foe how to use constraints...
     

    Attached Files:

  2. krishna84

    krishna84 Active Member

    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    75
    Location:
    hyderabad
    hi please find this.
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    Why? At the start of this document the description of the unique constraint is incorrect ("The column(s) declared Unique does not allow duplication of values but can contain NULL values. ") as multi-column unique constraints cannot have duplicate KEYS but that does not prevent any part of any key from duplicating values found in any other valid key:

    Code (SQL):
    SQL> ALTER TABLE emp ADD CONSTRAINT enp_uq UNIQUE (empno, ename);
     
    TABLE altered.
     
    SQL>
    SQL> COLUMN maxemp new_value max_eno
    SQL>
    SQL> SELECT MAX(empno) maxemp FROM emp;
     
        MAXEMP
    ----------
          7934
     
    SQL>
    SQL> DECLARE
      2          start_eno NUMBER:=&max_eno + 1;
      3  BEGIN
      4          FOR i IN start_eno .. 8000 loop
      5                  INSERT INTO emp
      6                  VALUES (i, 'MARVA','LACKEY', 7734, sysdate-i, 990, 0, 20);
      7          END loop;
      8
      9          commit;
     10
     11  END;
     12  /
    OLD   2:        start_eno NUMBER:=&max_eno + 1;
    NEW   2:        start_eno NUMBER:=      7934 + 1;
     
    PL/SQL PROCEDURE successfully completed.
     
    SQL>
    SQL> SELECT empno, ename
      2  FROM emp
      3  WHERE ename = 'MARVA';
     
         EMPNO ENAME
    ---------- ----------
          7935 MARVA
          7936 MARVA
          7937 MARVA
          7938 MARVA
          7939 MARVA
          7940 MARVA
          7941 MARVA
          7942 MARVA
          7943 MARVA
          7944 MARVA
          7945 MARVA
     
         EMPNO ENAME
    ---------- ----------
          7946 MARVA
          7947 MARVA
          7948 MARVA
          7949 MARVA
          7950 MARVA
          7951 MARVA
          7952 MARVA
          7953 MARVA
          7954 MARVA
          7955 MARVA
          7956 MARVA
     
         EMPNO ENAME
    ---------- ----------
          7957 MARVA
          7958 MARVA
          7959 MARVA
          7960 MARVA
          7961 MARVA
          7962 MARVA
          7963 MARVA
          7964 MARVA
          7965 MARVA
          7966 MARVA
          7967 MARVA
     
         EMPNO ENAME
    ---------- ----------
          7968 MARVA
          7969 MARVA
          7970 MARVA
          7971 MARVA
          7972 MARVA
          7973 MARVA
          7974 MARVA
          7975 MARVA
          7976 MARVA
          7977 MARVA
          7978 MARVA
     
         EMPNO ENAME
    ---------- ----------
          7979 MARVA
          7980 MARVA
          7981 MARVA
          7982 MARVA
          7983 MARVA
          7984 MARVA
          7985 MARVA
          7986 MARVA
          7987 MARVA
          7988 MARVA
          7989 MARVA
     
         EMPNO ENAME
    ---------- ----------
          7990 MARVA
          7991 MARVA
          7992 MARVA
          7993 MARVA
          7994 MARVA
          7995 MARVA
          7996 MARVA
          7997 MARVA
          7998 MARVA
          7999 MARVA
          8000 MARVA
     
    66 ROWS selected.
     
    SQL>
    66 valid unique keys all with the same ename value.

    I think your document needs a bit of work on clarifying such information.