Inserting multiple records into sqlite and ignore duplicates

[topcat01]

Hi,

I wrote the following code to create two tables, add a couple of triggers, the problem I am having is with the add_props_date trigger... when an existing record is already present in the table email i still want a record creating in the email props table, which works however the compid field is showing as -1 I need compid to have the value of hashid (the record which already exists). If i get 10 duplicate entries into the database, there will be only one record in the email table and 10 references to that in emailprops table


Hope this makes sense, here is my code:

CREATE TABLE email ( 
    hashid       INTEGER PRIMARY KEY, 
    emailhash  TEXT	NOT NULL UNIQUE,
	hash_date_added DATETIME
);


CREATE TABLE emailprops ( 
    propsid		INTEGER PRIMARY KEY, 
    compid	    INTEGER, 
    props_date_added	DATETIME,
    FOREIGN KEY (compid) REFERENCES email(hashid)
);


CREATE TRIGGER add_hash_date AFTER INSERT ON email  
BEGIN  
    UPDATE email SET hash_date_added = DATETIME('NOW') WHERE hashid = new.hashid;  
END;

CREATE TRIGGER add_props_date BEFORE INSERT ON email
BEGIN
   INSERT INTO emailprops(compid, props_date_added) VALUES (new.hashid, datetime('now'));
END;



INSERT INTO email ( emailhash ) VALUES ( 'hello' );
INSERT OR IGNORE INTO email ( emailhash ) VALUES ( 'hello' );

Here are some screen shots showing what I get:

And this is what i want (with a little creative editing):

Thanks!

 

[Andrzejek]

Just a guess here...
I would set the default values for the [tt]date_added[/tt] fields instead of the trigger:

[pre]
CREATE TABLE email (
hashid INTEGER PRIMARY KEY,
emailhash TEXT NOT NULL UNIQUE,
hash_date_added DATETIME NOT NULL DFAULT DATETIME('NOW')
);

CREATE TABLE emailprops (
propsid INTEGER PRIMARY KEY,
compid INTEGER,
props_date_added DATETIME NOT NULL DFAULT DATETIME('NOW'),
FOREIGN KEY (compid) REFERENCES email(hashid)
);
[/pre]
And there is nothing to increment and keep unique your Primary Keys in both tables.

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[topcat01]

Thanks that seems like common sense! I have revised the code as follows:

CREATE TABLE email ( 
    hashid       INTEGER PRIMARY KEY, 
    emailaddresshash  TEXT	NOT NULL UNIQUE,
	hash_date_added DATETIME	NOT NULL DEFAULT (DATETIME('NOW'))
);

CREATE TABLE emailprops ( 
    propsid		INTEGER PRIMARY KEY, 
    compid	    INTEGER, 
    props_date_added	DATETIME	NOT NULL DEFAULT (DATETIME('NOW')),
    FOREIGN KEY (compid) REFERENCES email(hashid)
);

CREATE TRIGGER add_index BEFORE INSERT ON email
BEGIN  
    INSERT INTO emailprops (compid) VALUES (email.hashid);
END;

INSERT INTO email ( emailaddresshash ) VALUES ( 'junk@test.com' );

However I now get an error when trying to add the value to compid

SQLite3 Error 1 - no such column: email.hashid

I need the hashid from the table email inserted into table emailprops everytime an existing record is added or already present in the table email.

This is the pseudocode if it makes sense:
try to add record to table email
if record does not exist in table email then add to table email, then add record to table emailprops
if record does exist in table email then do not add record to table email, then add record to table emailprops


Thanks

 

[Andrzejek]

Well, you have here Parent (email) - Child (emailprops) relationship between the 2 tables. So in order to INSERT any record into emailprops table, you need to have a record in the email table. But how do you know which record in emailprops table relates to which record in email table before you insert the record into emailprops table? And which hashid value to use as the value for compid in emailprops table? I don't think you can have this logic in the definition of the tables, you need to have it in the code that deals with [tt]INSERT INTO emailprops[/tt] statements.

Your 'pseudocode' logic may theoretically work if you always insert a record into the emailprops table related to the last record in email table, but is that always the case? You may have 100 records in email table, and you want to insert a record into emailprops table related to the 27th record in email table. Which hashid value do you use then?

Also, Access uses something called 'AutoNumber' field as PK for a table, Oracle and SQL Server uses sequences to increment PK value in the tables. I would assume SQLite also uses some kind of a sequence for PK

Have fun.

---- Andy

There is a great need for a sarcasm font.

 

[topcat01]

Thanks for the reply, I need to look at the autonumbering and see how that can help or look at another way completely. Thanks very much for the suggestions!