Fedora Linux Support Community & Resources Center
  #1  
Old 15th February 2009, 07:08 AM
premudriy Offline
Registered User
 
Join Date: Dec 2006
Posts: 313
Unhappy SQLite: Insert unique records into a table WITHOUT primary key?

Hello everyone,


I'm stuck on this sqlite assignment:

Let's say there is a table "employees" which contains: employeeName, spouseName, address

The "employeeName" is assumed to be a primary key, but table's .sql schema does NOT have a primary key specified on purpose (very weird assignment).


I need to insert, however, unique records, so like if I insert tuple "John Doe, Marry J, someAddress" 2 times, then table will contain only one John Doe.

I need to do it with a single sqlite "insert" statement.


I've tried using "INSERT OR REPLACE blah blah blah" and "INSERT OR IGNORE blah blah blah", but they these statements didn't work - duplicates were still inserted.



I'm almost done with my assignment, but I spent like 8 hours already trying to solve this last problem and still can't find anything that would work.


Please, help!
Thank you!
Reply With Quote
  #2  
Old 15th February 2009, 09:10 AM
dr death Offline
Registered User
 
Join Date: Dec 2006
Posts: 82
Well, the "INSERT OR REPLACE" statement only does the REPLACE on a conflict. Without a conflict it will just insert.

If you can put a UNIQUE constraint on the employeeName column, then such a conflict will be generated - this is the way the INSERT OR REPLACE is designed to work.
Code:
sqlite> create table tbl(name text unique, address text);
sqlite> insert into tbl values('john', 'someaddr');
sqlite> insert into tbl values('john', 'someaddr1');
SQL error: column name is not unique
sqlite> insert or replace into tbl values('john', 'someaddr1');
sqlite> select * from tbl;
john|someaddr1
If you can't put that constraint there, then I don't know how to do it with a single insert statement.
Reply With Quote
  #3  
Old 15th February 2009, 09:30 AM
premudriy Offline
Registered User
 
Join Date: Dec 2006
Posts: 313
The only problem is that "unique" is not specified in the schema. Teacher provided the .sql file with schema and I must not modify it. And there are just attributes in that schema, so no "unique" or "primary key" statements.
Reply With Quote
  #4  
Old 15th February 2009, 10:20 AM
premudriy Offline
Registered User
 
Join Date: Dec 2006
Posts: 313
I guess I'm pretty screwed as of now. All methods that I've found in all these hours of searching need primary keys to be defined. I will talk to the teacher. I wonder if he actually forgot to add primary keys. He specifically said though that students must not modify the given .sql file.
Reply With Quote
  #5  
Old 15th February 2009, 12:02 PM
daverj Offline
Registered User
 
Join Date: Jan 2006
Location: Denver, CO USA
Posts: 670
you can do it without a primary key or a unique constraint by using a trigger to check the value of employeeName before the new record is inserted. Then do whatever you want.

something like this might work:
Code:
CREATE TRIGGER unique_row BEFORE INSERT ON employees
BEGIN
    DELETE FROM employees WHERE old.employeeName = new.employeeName;
END;
What this basically does is check to see if there is already a row containing the employeeName you want to enter. If one does exist it removes the current row. Then it inserts the new record. So effectively, it is replacing the current record with the new record. In the end only one record exists. However, it will contain the new values for spouseName and address.

davidj

Last edited by daverj; 15th February 2009 at 12:06 PM. Reason: fixed syntax error
Reply With Quote
  #6  
Old 15th February 2009, 02:28 PM
brebs Offline
Banned
 
Join Date: Apr 2008
Posts: 558
Quote:
Originally Posted by premudriy View Post
employeeName, spouseName, address
Tell your teacher that the design of the table is wrong, according to relational database design. There should be an "employeeid" field, which is an AUTONUMBER (i.e. created by the database) with a primary key on it. *That* is the primary key.

It is perfectly feasible that two people in a company have the same name, e.g. "John Smith". So assuming that this is impossible, is a fundamental error.

So, how to distinguish the two John Smiths? Could add a unique key (unique, but *not* the primary key, because the primary key is employeeid) on employeename & their postcode, for example - that's a fairly safe bet. Some compromise has to be made.

BTW, the "address" should be split up into different fields, e.g postcode or zipcode, whatever it's called in your country.

Last edited by brebs; 15th February 2009 at 02:33 PM.
Reply With Quote
  #7  
Old 15th February 2009, 11:55 PM
premudriy Offline
Registered User
 
Join Date: Dec 2006
Posts: 313
Daverj, I'll probably end up doing what you've told. The only thing that puzzles me is that assignment states: "Create an INSERT statement that inserts new value into the table if it's not already there." I don't know if that means to use the "insert" statement only and no triggers. But as of now I'm pretty exhausted with it and I'll just do a trigger. Thanks!


Brebs, I completely agree with you. According to the book, all tables must have a primary key, which also must be unique, or at least a foreign key. In my case there are no keys at all. Maybe our teacher just want to make us thing outside of the box, but this assignment drives me nuts.
Reply With Quote
Reply

Tags
insert, key, primary, records, sqlite, table, unique

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Fedora Core 9 (FC9) Yum download primary.sqlite.bz2 problem BardiaN Installation, Upgrades and Live Media 3 17th April 2009 02:22 PM
Python+SQLite: how do I search the whole table(all columns)? premudriy Programming & Packaging 3 12th April 2009 07:46 AM
F7 - primary.sqlite.bz2 jsabarese Using Fedora 0 10th November 2007 09:33 AM
Why yum frequently downloads "primary.sqlite.bz2" spremi Using Fedora 4 5th September 2007 05:23 PM


Current GMT-time: 14:26 (Thursday, 23-10-2014)

TopSubscribe to XML RSS for all Threads in all ForumsFedoraForumDotOrg Archive
logo

All trademarks, and forum posts in this site are property of their respective owner(s).
FedoraForum.org is privately owned and is not directly sponsored by the Fedora Project or Red Hat, Inc.

Privacy Policy | Term of Use | Posting Guidelines | Archive | Contact Us | Founding Members

Powered by vBulletin® Copyright ©2000 - 2012, vBulletin Solutions, Inc.

FedoraForum is Powered by RedHat
Mazatlan Travel Photos on Instagram - Saoner Photos - Golden Gate Travel Photos