Welcome to WebmasterWorld Guest from

Forum Moderators: open

Message Too Old, No Replies

constraint on import

2:53 pm on Apr 5, 2011 (gmt 0)

Preferred Member

10+ Year Member

joined:June 16, 2005
posts: 486
votes: 0

I import data regularly form the following function:
load data local infile 'C:\\Documents and Settings\\J\\Desktop\\import.csv' into table data fields terminated by ',' enclosed by '''' lines terminated by '\n';

The table structure is:
`Date` datetime DEFAULT NULL,
`Symbol` varchar(6) DEFAULT NULL,
`ClosingPrice` decimal(8,2) DEFAULT NULL,
KEY `Symbol` (`Symbol`)

I cannot edit all of the raw data so what I want to so is prevent duplicates entering the database. A duplicate is a row that has the same same symbol and same date. So, 1 entry for AAA '2011-04-01' is allowed but not
AAA '2011-04-01' & AAA '2011-04-01'

What is the best way to put a constraint on this while importing?
Furthermore, I would like to allow the data be updated if the ClosingPrice has changed.

I thought a trigger might work:
create trigger duplicate_check before insert on data for each row
if new.Date = (SELECT Date FROM data WHERE Date=new.Date AND Symbol=new.Symbol) then
call fail(’Duplicate data’);
#is there a way just to abort that 1 row insert here?
#where is the else function to allow the insert?
end if;
end $$

Where is the else function to allow the insert?
Secondly, as the imported data doesn't have column labels how does the insert know which column is which as at the moment it just imports them in column order I think.