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!

Value constraint

Discussion in 'SQL PL/SQL' started by pss, Mar 8, 2014.

  1. pss

    pss Guest

    Hi,

    In a table I don't want a value from an attribute to be repeated more than 7 times. With which constraint code can I do this?
    It's like a movie but that movie can't have more than 7 subtitles. The table only have the attributes movie_ID and s_language.

    Thanks
     
  2. yowancristo

    yowancristo Forum Advisor

    Messages:
    103
    Likes Received:
    10
    Trophy Points:
    305
    Location:
    Bangalore
    Hi,

    Database Trigger can be used for this, something like below may work.

    Code (SQL):

    CREATE    OR    REPLACE TRIGGER    XXC_CHECK_COUNT_ATTRIBUTE
    AFTER INSERT   ON <Table_Name>
    DECLARE
        l_count    NUMBER;
        ew          EXCEPTION;
    BEGIN

        SELECT    COUNT(attribute_name)    INTO    l_count    FROM    <Table_Name>;
       
        IF l_count > 3    THEN
            RAISE ew;
        END IF;

    END;
    /
     
    Regards,
    Yowan Cristo:hurray
     
  3. zargon

    zargon Community Moderator Forum Guru

    Messages:
    2,347
    Likes Received:
    348
    Trophy Points:
    1,430
    Location:
    Aurora, CO
    It really won't since there can be several inserts at once that won't be caught by the trigger; this similar example tries to catch duplicate records using a trigger and it fails:


    Code (SQL):

    SESSION #1 --


    SQL> CREATE TABLE dup_test(
      2          id      NUMBER,
      3          DATA    varchar2(40)
      4  );


    TABLE created.


    SQL>
    SQL> CREATE OR REPLACE TRIGGER chk_for_dups_trg
      2  BEFORE INSERT ON dup_test
      3  FOR each ROW
      4  DECLARE
      5          rowct   NUMBER;
      6  BEGIN
      7          SELECT COUNT(*) INTO rowct FROM dup_test
      8          WHERE id = :NEW.id;
      9
     10          IF rowct <> 0 THEN
     11                  raise_application_error(-20998, 'Duplicate key discovered.');
     12          END IF;
     13
     14  END;
     15  /


    TRIGGER created.


    SQL>
    SQL> SHOW errors

    No errors.

    SQL>
    SQL> BEGIN
      2          FOR i IN 1..100 loop
      3                  INSERT INTO dup_test
      4                  VALUES(i, 'Snorthorfer pingnoodle '||i);
      5          END loop;
      6
      7          commit;
      8
      9  END;
     10  /


    PL/SQL PROCEDURE successfully completed.


    SQL>
    SQL> SELECT * FROM dup_test;


            ID DATA
    ---------- ----------------------------------------
             1 Snorthorfer pingnoodle 1
             2 Snorthorfer pingnoodle 2
             3 Snorthorfer pingnoodle 3
             4 Snorthorfer pingnoodle 4
             5 Snorthorfer pingnoodle 5
             6 Snorthorfer pingnoodle 6
             7 Snorthorfer pingnoodle 7
             8 Snorthorfer pingnoodle 8
             9 Snorthorfer pingnoodle 9
            10 Snorthorfer pingnoodle 10
            11 Snorthorfer pingnoodle 11
            12 Snorthorfer pingnoodle 12
            13 Snorthorfer pingnoodle 13
            14 Snorthorfer pingnoodle 14
            15 Snorthorfer pingnoodle 15
            16 Snorthorfer pingnoodle 16
            17 Snorthorfer pingnoodle 17
            18 Snorthorfer pingnoodle 18
            19 Snorthorfer pingnoodle 19
            20 Snorthorfer pingnoodle 20
            21 Snorthorfer pingnoodle 21
            22 Snorthorfer pingnoodle 22
            23 Snorthorfer pingnoodle 23
            24 Snorthorfer pingnoodle 24
            25 Snorthorfer pingnoodle 25
            26 Snorthorfer pingnoodle 26
            27 Snorthorfer pingnoodle 27
            28 Snorthorfer pingnoodle 28
            29 Snorthorfer pingnoodle 29
            30 Snorthorfer pingnoodle 30
            31 Snorthorfer pingnoodle 31
            32 Snorthorfer pingnoodle 32
            33 Snorthorfer pingnoodle 33
            34 Snorthorfer pingnoodle 34
            35 Snorthorfer pingnoodle 35
            36 Snorthorfer pingnoodle 36
            37 Snorthorfer pingnoodle 37
            38 Snorthorfer pingnoodle 38
            39 Snorthorfer pingnoodle 39
            40 Snorthorfer pingnoodle 40
            41 Snorthorfer pingnoodle 41
            42 Snorthorfer pingnoodle 42
            43 Snorthorfer pingnoodle 43
            44 Snorthorfer pingnoodle 44
            45 Snorthorfer pingnoodle 45
            46 Snorthorfer pingnoodle 46
            47 Snorthorfer pingnoodle 47
            48 Snorthorfer pingnoodle 48
            49 Snorthorfer pingnoodle 49
            50 Snorthorfer pingnoodle 50
            51 Snorthorfer pingnoodle 51
            52 Snorthorfer pingnoodle 52
            53 Snorthorfer pingnoodle 53
            54 Snorthorfer pingnoodle 54
            55 Snorthorfer pingnoodle 55
            56 Snorthorfer pingnoodle 56
            57 Snorthorfer pingnoodle 57
            58 Snorthorfer pingnoodle 58
            59 Snorthorfer pingnoodle 59
            60 Snorthorfer pingnoodle 60
            61 Snorthorfer pingnoodle 61
            62 Snorthorfer pingnoodle 62
            63 Snorthorfer pingnoodle 63
            64 Snorthorfer pingnoodle 64
            65 Snorthorfer pingnoodle 65
            66 Snorthorfer pingnoodle 66
            67 Snorthorfer pingnoodle 67
            68 Snorthorfer pingnoodle 68
            69 Snorthorfer pingnoodle 69
            70 Snorthorfer pingnoodle 70
            71 Snorthorfer pingnoodle 71
            72 Snorthorfer pingnoodle 72
            73 Snorthorfer pingnoodle 73
            74 Snorthorfer pingnoodle 74
            75 Snorthorfer pingnoodle 75
            76 Snorthorfer pingnoodle 76
            77 Snorthorfer pingnoodle 77
            78 Snorthorfer pingnoodle 78
            79 Snorthorfer pingnoodle 79
            80 Snorthorfer pingnoodle 80
            81 Snorthorfer pingnoodle 81
            82 Snorthorfer pingnoodle 82
            83 Snorthorfer pingnoodle 83
            84 Snorthorfer pingnoodle 84
            85 Snorthorfer pingnoodle 85
            86 Snorthorfer pingnoodle 86
            87 Snorthorfer pingnoodle 87
            88 Snorthorfer pingnoodle 88
            89 Snorthorfer pingnoodle 89
            90 Snorthorfer pingnoodle 90
            91 Snorthorfer pingnoodle 91
            92 Snorthorfer pingnoodle 92
            93 Snorthorfer pingnoodle 93
            94 Snorthorfer pingnoodle 94
            95 Snorthorfer pingnoodle 95
            96 Snorthorfer pingnoodle 96
            97 Snorthorfer pingnoodle 97
            98 Snorthorfer pingnoodle 98
            99 Snorthorfer pingnoodle 99
           100 Snorthorfer pingnoodle 100


    100 ROWS selected.


    SQL>
    SQL> INSERT INTO dup_test
      2  VALUES(101, 'Snotrhorfer pingnoodle 101');


    1 ROW created.


    SQL>
    SQL> pause
     

    Now a second session is started and executes the exact same insert as before, and this second, duplicate insert is committed:


    Code (SQL):

    SESSION 2 --


    SQL> INSERT INTO dup_test
      2  VALUES(101, 'Snotrhorfer pingnoodle 101');


    1 ROW created.

    Commit complete.


    SQL>
     

    Now, going back to Session 1 we complete the script and see what happens:


    Code (SQL):

    SESSION 1 --


    SQL> commit;
    Commit complete.
    SQL>
    SQL> SELECT * FROM dup_test;


            ID DATA                                                                
    ---------- ----------------------------------------                            
             1 Snorthorfer pingnoodle 1                                            
             2 Snorthorfer pingnoodle 2                                            
             3 Snorthorfer pingnoodle 3                                            
             4 Snorthorfer pingnoodle 4                                            
             5 Snorthorfer pingnoodle 5                                            
             6 Snorthorfer pingnoodle 6                                            
             7 Snorthorfer pingnoodle 7                                            
             8 Snorthorfer pingnoodle 8                                            
             9 Snorthorfer pingnoodle 9                                            
            10 Snorthorfer pingnoodle 10                                            
            11 Snorthorfer pingnoodle 11                                            
            12 Snorthorfer pingnoodle 12                                            
            13 Snorthorfer pingnoodle 13                                            
            14 Snorthorfer pingnoodle 14                                            
            15 Snorthorfer pingnoodle 15                                            
            16 Snorthorfer pingnoodle 16                                            
            17 Snorthorfer pingnoodle 17                                            
            18 Snorthorfer pingnoodle 18                                            
            19 Snorthorfer pingnoodle 19                                            
            20 Snorthorfer pingnoodle 20                                            
            21 Snorthorfer pingnoodle 21                                            
            22 Snorthorfer pingnoodle 22                                            
            23 Snorthorfer pingnoodle 23                                            
            24 Snorthorfer pingnoodle 24                                            
            25 Snorthorfer pingnoodle 25                                            
            26 Snorthorfer pingnoodle 26                                            
            27 Snorthorfer pingnoodle 27                                            
            28 Snorthorfer pingnoodle 28                                            
            29 Snorthorfer pingnoodle 29                                            
            30 Snorthorfer pingnoodle 30                                            
            31 Snorthorfer pingnoodle 31                                            
            32 Snorthorfer pingnoodle 32                                            
            33 Snorthorfer pingnoodle 33                                            
            34 Snorthorfer pingnoodle 34                                            
            35 Snorthorfer pingnoodle 35                                            
            36 Snorthorfer pingnoodle 36                                            
            37 Snorthorfer pingnoodle 37                                            
            38 Snorthorfer pingnoodle 38                                            
            39 Snorthorfer pingnoodle 39                                            
            40 Snorthorfer pingnoodle 40                                            
            41 Snorthorfer pingnoodle 41                                            
            42 Snorthorfer pingnoodle 42                                            
            43 Snorthorfer pingnoodle 43                                            
            44 Snorthorfer pingnoodle 44                                            
            45 Snorthorfer pingnoodle 45                                            
            46 Snorthorfer pingnoodle 46                                            
            47 Snorthorfer pingnoodle 47                                            
            48 Snorthorfer pingnoodle 48                                            
            49 Snorthorfer pingnoodle 49                                            
            50 Snorthorfer pingnoodle 50                                            
            51 Snorthorfer pingnoodle 51                                            
            52 Snorthorfer pingnoodle 52                                            
            53 Snorthorfer pingnoodle 53                                            
            54 Snorthorfer pingnoodle 54                                            
            55 Snorthorfer pingnoodle 55                                            
            56 Snorthorfer pingnoodle 56                                            
            57 Snorthorfer pingnoodle 57                                            
            58 Snorthorfer pingnoodle 58                                            
            59 Snorthorfer pingnoodle 59                                            
            60 Snorthorfer pingnoodle 60                                            
            61 Snorthorfer pingnoodle 61                                            
            62 Snorthorfer pingnoodle 62                                            
            63 Snorthorfer pingnoodle 63                                            
            64 Snorthorfer pingnoodle 64                                            
            65 Snorthorfer pingnoodle 65                                            
            66 Snorthorfer pingnoodle 66                                            
            67 Snorthorfer pingnoodle 67                                            
            68 Snorthorfer pingnoodle 68                                            
            69 Snorthorfer pingnoodle 69                                            
            70 Snorthorfer pingnoodle 70                                            
            71 Snorthorfer pingnoodle 71                                            
            72 Snorthorfer pingnoodle 72                                            
            73 Snorthorfer pingnoodle 73                                            
            74 Snorthorfer pingnoodle 74                                            
            75 Snorthorfer pingnoodle 75                                            
            76 Snorthorfer pingnoodle 76                                            
            77 Snorthorfer pingnoodle 77                                            
            78 Snorthorfer pingnoodle 78                                            
            79 Snorthorfer pingnoodle 79                                            
            80 Snorthorfer pingnoodle 80                                            
            81 Snorthorfer pingnoodle 81                                            
            82 Snorthorfer pingnoodle 82                                            
            83 Snorthorfer pingnoodle 83                                            
            84 Snorthorfer pingnoodle 84                                            
            85 Snorthorfer pingnoodle 85                                            
            86 Snorthorfer pingnoodle 86                                            
            87 Snorthorfer pingnoodle 87                                            
            88 Snorthorfer pingnoodle 88                                            
            89 Snorthorfer pingnoodle 89                                            
            90 Snorthorfer pingnoodle 90                                            
            91 Snorthorfer pingnoodle 91                                            
            92 Snorthorfer pingnoodle 92                                            
            93 Snorthorfer pingnoodle 93                                            
            94 Snorthorfer pingnoodle 94                                            
            95 Snorthorfer pingnoodle 95                                            
            96 Snorthorfer pingnoodle 96                                            
            97 Snorthorfer pingnoodle 97                                            
            98 Snorthorfer pingnoodle 98                                            
            99 Snorthorfer pingnoodle 99                                            
           100 Snorthorfer pingnoodle 100                                          
           101 Snotrhorfer pingnoodle 101                                          
           101 Snorthorfer pingnoodle 101                                          


    102 ROWS selected.


    SQL>
     

    Triggers are transactional, and if a trigger cannot enforce uniqueness it cannot enforce the existence of a given number of records.


    To enforce such a 'constraint' it's possible to use a materialized view, as illustrated in this similar example:


    Code (SQL):

    SQL> CREATE TABLE one_row(
      2  id NUMBER,
      3  stuff      varchar2(40));


    TABLE created.


    SQL>
    SQL> ALTER TABLE one_row
      2  ADD CONSTRAINT onerow_pk PRIMARY KEY(id);


    TABLE altered.


    SQL>
    SQL> INSERT INTO one_row
      2  VALUES(1,'When in the course of human events');


    1 ROW created.


    SQL>
    SQL> commit;


    Commit complete.


    SQL>
    SQL> CREATE materialized VIEW log ON one_row;


    Materialized VIEW log created.


    SQL>
    SQL> CREATE materialized VIEW onerow_vw
      2  build immediate
      3  refresh complete ON commit AS
      4  SELECT COUNT(*) rowct
      5  FROM one_row;


    Materialized VIEW created.


    SQL>
    SQL> SELECT * FROM one_row;


            ID STUFF
    ---------- ----------------------------------------
             1 WHEN IN the course OF human events


    SQL>
    SQL> ALTER TABLE onerow_vw
      2  ADD CONSTRAINT row_count_chk
      3  CHECK(rowct <= 1)
      4  deferrable;


    TABLE altered.


    SQL>
    SQL> SELECT * FROM one_row;


            ID STUFF
    ---------- ----------------------------------------
             1 WHEN IN the course OF human events


    SQL>
    SQL> INSERT INTO one_row
      2  VALUES(2,'When in the course of human events');


    1 ROW created.


    SQL>
    SQL> commit;
    commit
    *
    ERROR at line 1:
    ORA-12008: error IN materialized VIEW refresh path
    ORA-02290: CHECK CONSTRAINT (BINGNORFER.ROW_COUNT_CHK) violated

    SQL>
    SQL> SELECT * FROM one_row;


            ID STUFF
    ---------- ----------------------------------------
             1 WHEN IN the course OF human events


    SQL>
     

    The desired result is obtained, but not with a trigger.
     
    yowancristo likes this.