Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2008
    Posts
    26

    Question Unanswered: i want only two duplicate rows in a column

    hi,


    i want only two duplicate rows in a column not more than tat.
    shall i use a check constraint for tat. how should i do dis???

    can u give me some suggestions???????

    srno item description
    1 ita italy
    1 irn iram
    2 ind india
    2 usa usa

    this is how my table should be.
    It should not allow the third entry for srno=1
    how should i do this?????

  2. #2
    Join Date
    Nov 2008
    Posts
    1
    Generally this kind of validation will be implemented on application development side. not database side.

    Cheers,
    Sengwa

  3. #3
    Join Date
    Jan 2004
    Location
    Croatia, Europe
    Posts
    4,094
    Provided Answers: 4
    Generally, I would disagree with you, @nicholas_ejn. I'd always rather do it on database level than in application. In this case, as "normal" integrity constraints can not enforce such a business rule, I'd do it using a database trigger.

    Here's an example: first, create a test table and a trigger:
    Code:
    SQL> CREATE TABLE TEST
      2  (srno NUMBER,
      3   item VARCHAR2(3),
      4   description VARCHAR2(10)
      5  );
    
    Table created.
    
    SQL> CREATE OR REPLACE TRIGGER trg_bi_test
      2    BEFORE UPDATE OR INSERT
      3    ON TEST
      4    FOR EACH ROW
      5  DECLARE
      6    l_cnt NUMBER;
      7  BEGIN
      8    SELECT COUNT(*) INTO l_cnt
      9      FROM TEST
     10      WHERE srno = :NEW.srno;
     11
     12    IF l_cnt >= 2 THEN
     13       RAISE_APPLICATION_ERROR
     14         (-20001, 'There are already two SRNO = ' || :NEW.srno);
     15    END IF;
     16  END;
     17  /
    
    Trigger created.
    
    SQL>
    Now, let's test it (note: the third insert where SRNO = 1 will fail):
    Code:
    SQL> INSERT INTO TEST (srno, item, description) VALUES (1, 'ita', 'italy');
    
    1 row created.
    
    SQL> INSERT INTO TEST (srno, item, description) VALUES (1, 'irn', 'iran');
    
    1 row created.
    
    SQL> INSERT INTO TEST (srno, item, description) VALUES (1, 'cro', 'croatia');
    INSERT INTO TEST (srno, item, description) VALUES (1, 'cro', 'croatia')
                *
    ERROR at line 1:
    ORA-20001: There are already two SRNO = 1
    ORA-06512: at "SCOTT.TRG_BI_TEST", line 9
    ORA-04088: error during execution of trigger 'SCOTT.TRG_BI_TEST'
    
    
    SQL> INSERT INTO TEST (srno, item, description) VALUES (2, 'ind', 'india');
    
    1 row created.
    
    SQL>

  4. #4
    Join Date
    Nov 2008
    Posts
    26
    hi

    thanks for the solution.......


    regards

    vaibhav174u

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •