preventing record from dupilcation

[malargopal]

Hi All,

In a table for example Employee_table , empid is set as primarykey.
When i insert duplicate records it is accepting the records.

sample data:
empid empname
101 A
101 B

But when we take this records in joining tables it is voilating.
I just want to get the error message when i insert data to the table.

Can any one please sort out htis problem.

Thanks,
Malar

[JimKnicely]

Hi,

How are you inserting data? With the INSERT statement? If so, you can add a little logic to the INSERT statement to exclude duplicate PK data.

Here's an example. Say I have the following table:

Code: Select all

dbadmin=> \d test.t1;
                                    List of Fields by Tables
 Schema | Table | Column |     Type     | Size | Default | Not Null | Primary Key | Foreign Key
--------+-------+--------+--------------+------+---------+----------+-------------+-------------
 test   | T1    | C1     | numeric(5,0) |    8 |         | t        | t           |
 test   | T1    | C2     | varchar(100) |  100 |         | f        | f           |
(2 rows)

dbadmin=> select * from test.t1;
 C1 |  C2
----+------
  1 | Jim
  2 | Jane
(2 rows)

If I run the following statement, the data will be inserted even though it'll violate the PK:

Code: Select all

dbadmin=> insert into test.t1 select 1, 'Madeline';
 OUTPUT
--------
      1
(1 row)

But, the next INSERT statement won't insert any data because it already exists in the table:

Code: Select all

dbadmin=> insert into test.t1 select 1, 'Madeline' from dual where not exists (select null from test.t1 where c1 = 1);
 OUTPUT
--------
      0
(1 row)

I hope this helps!

[pborne]

Primary Keys and Foreign Keys are not actively enforced in Vertica. As you noticed, you can insert duplicates and Vertica won't complain at insertion time. However, when you run a query with an inner join or an outer join for instance, you will get an error message if Vertica detects that you have a duplicate.

If you want to ask Vertica to check that your constraints are not violated, you need to use the ANALYZE_CONSTRAINTS() function before you commit.

Code: Select all

dbadmin=> create table test1 (k integer primary key, v varchar(10));
CREATE TABLE
dbadmin=> 

dbadmin=> insert into test1 values (1, 'value1');
 OUTPUT 
--------
      1
(1 row)

dbadmin=> insert into test1 values (1, 'value2');
 OUTPUT 
--------
      1
(1 row)

dbadmin=> select * from test1;
 k |   v    
---+--------
 1 | value1
 1 | value2
(2 rows)

dbadmin=> select analyze_constraints('test1');
 Schema Name | Table Name | Column Names | Constraint Name | Constraint Type | Column Values 
-------------+------------+--------------+-----------------+-----------------+---------------
 public      | test1      | k            | C_PRIMARY       | PRIMARY         | ('1')
(1 row)

If no violation was found, the result set would be empty. In the case above, Vertica is telling us that the PK is violated for k=1.

You can then rollback and figure out why you have a duplicate entry.

Code: Select all

dbadmin=> rollback;
ROLLBACK
dbadmin=> select * from test1;
 k | v 
---+---
(0 rows)

If you are loading data in bulk via the COPY command, you can do something similar by using the "NO COMMIT" option which won't commit automatically. You can then run analyze_constraints() and, based on the result, decide to commit or rollback or do some clean up automatically.