SQLite: Insert unique records into a table WITHOUT primary key?

[premudriy]

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!

[dr death]

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.

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.

[premudriy]

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.

[premudriy]

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.

[daverj]

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:

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

[brebs]

Originally Posted by premudriy

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.

[premudriy]

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.