More SQL(ite) questions - Duplicate keys lazarus / 16-Databases ---------------------------------------------------------------------- Pascaluvr · 2012-04-05 05:07 It seems that SQLite doesn't like duplicate keys, but in the real world, they exist - regardless of what James Martin used to say. So can I use ROWID to help make it unique? All examples that I see that define a ROWID-like column use AUTOINC and PRIMARY KEY together.  What I want is something like this: CREATE TABLE MyTables (   dKey TEXT,   data  TEXT,   PRIMARY KEY( dKey, ROWID) In SQLITE ROWID is created automatically and can be accessed by that name. Would this work?  I know I can test this myself, and will do so if there are no replies, I was just hoping someone had already tried something similar and perhaps short circuit any pitfalls I find along the way. Thanks again to all the great people here and their time and energy they put into helping others. ---------------------------------------------------------------------- BigChimp · 2012-04-05 06:40 Don't know what James Martin used to say There are 2 schools in relational db design: 1. Use artificial primary keys that are guaranteed to be unique but have no business sense. A lot of databases have "autoincrement columns" or "sequences" (or Firebird generators) that will generate unique numbers either "automatically" or using explicit triggers. 2. Use natural primary keys, if necessary compound keys of multiple columns that together are guaranteed to be unique. Because the 2nd approach depends on business requirements/specifications that may turn out to be false (e.g. US Social Security Numbers apparently don't turn out to be unique and not everybody has them), a lot of people go for option 1. It also requires less thinking, that's why I like it. Then, if you're smart, you can set an extra (compound) unique index on the natural key candidate columns (or whatever it's officially called). If the business defintions change, you can alter or remove the unique index without messing with the primary key/foreign key setup. SQLite AUTOINCREMENT apparently already uses ROWID... see: http://www.sqlite.org/autoinc.html ... and it is used for option 1. The entire point of this is that these numbers are guaranteed to be unique*). I don't know how or if your proposal would work... but if you are sure your dKey column is unique you could use that (i.e. option 2). Otherwise, I'd just leave it as the samples with autoincr suggest and set a unique index on your candidate key dKey. *) A problem with this is if you have multiple stand alone databases that need to synchronize/replicate once in a while. Because the dbs don't talk all the time (and that introduces a single point of failure anyway), you can't be sure the primary keys are unique using e.g. (I think - brief glance) SQLite's algorithm. A solution for that is using an algorithm specially written to give random or non-conflicting numbers - e.g. generating GUID - Globally Unique Identifiers, or prefixing the primary key with a domain/database identifier. Of course, having different PKs does not solve the problem of users entering/editing the same row (same in a business/data sense) twice... but that's another problem. However, if you're using SQlite, I suppose these problems will not be that pressing. ---------------------------------------------------------------------- ludob · 2012-04-05 07:54 QuoteCode: [Select]CREATE TABLE MyTables (   dKey TEXT,   data  TEXT,   PRIMARY KEY( dKey, ROWID) This works but has several disadvantages: - multiple column indexes have an overhead, are slower and are stored separately (it is a different index). - since there is no column that refers to ROWID, a SELECT * FROM MyTables  doesn't return ROWID and you'll have problems using the primary index for updates. How are you going to update one of the duplicate dKey records when you don't have the ROWID? Comparing all fields of the record is a possibility but also a gamble. - the hidden ROWID is very sqlite specific. Porting to another database is going to be difficult. As BigChimp explained, if you don't have a natural primary index (which is almost always the case), you have to add one: Code: [Select]CREATE TABLE MyTables (   ID INTEGER PRIMARY KEY,   dKey TEXT,   data  TEXT   ) In sqlite, because ID is an INTEGER PRIMARY KEY it will map to ROWID. So there is not cost at adding this index. In your application you use the ID for updates. When inserting new records, just keep the ROWID field empty (NULL). SQLite will create a new value automatically.  If you want your ID to be in strictly ascending order add the AUTOINCREMENT keyword: INTEGER PRIMARY KEY AUTOINCREMENT. You can hide ID to the user if you want. Regarding the naming of the primary key there also different schools: - use the same name (ID,...) for every column - use a combination of a fixed prefix and a reference to the table. FE. a table "users' would have a primary key "ID_user". The use of the plural "users" and singular "ID_user" makes reading SQL queries also more natural. Adopting a standard will make live much easier when writing queries, especially when multiple tables and foreign keys are involved. In a few weeks there is no way you still remember the key names of all your tables if you name them randomly. ---------------------------------------------------------------------- Pascaluvr · 2012-04-05 08:52 ROFLMAO Big Chimp, with your last response you could have stood on stage and supported him:) James Martin was the DB guru of the 70's, 80's (and maybe the 90's).  An ex IBM employee that made a fortune with his books and lecture circuit about relational DBMS before we had the hardware to implement it - so while overwhelming us all with the 'future', would then go into 'sales' mode of the then IBM offering (DL1/IMS) as the only way to go until better hardware was available.  (I vaguely remember that in the then theoretical model for RDBMS of the time, keys as such should not exist).  Accepting that we didn't have the hardware (then) to be able to support (large) databases that weren't indexed, he grudgingly accepted keys/indexes were ok as long as the key itself was 'meaningless' in the real world - say, like a ROWID or CustomerNumber - but as you point out, there are duplicate SSNs and also people without SSNs.  Anyway, thats a long time ago. I do agree with the 'meaningless' key idea, but that limits our access points to the data.  We still need access via meaningful keys.  For example, if I go to a bank and don't have my bank account details with me, they can still find my details using my name and asking a couple of questions.  I have no idea what my Tax File Number is, but I assure you the Taxation Department can find me in a few seconds without it. So in this imperfect world we sometimes do need to use meaningful data as a key, even if we ensure its unique by adding a ROWID (or something similar). Now, back to my original question.  1) I need to use meaningful data as a key. 2) Since it may not be unique, I want to add ROWID to the key to make it unique. 3) SQLite examples only show ROWID as AUTOINC PRIMARY KEY (if defined)    I want ROWID as AUTOINC, but not the primary key.  My Primary Key will be my 'meaningful data field' plus the ROWID to make it unique. That is the main point of my question - can I do this? ---------------------------------------------------------------------- Pascaluvr · 2012-04-05 09:12 hi ludob, Thank you for the reply. Starting at the easiest point, yes I do follow a sort of convention regarding names in SQL (and this is only after a week or 2 of playing) 1)  Tables are always plural, as in Customers 2) Fields are singular, as in customer 3) Tables have a caps first character - Customer 4) Fields may have caps, but not first, eg customerNo Back to the specific issue - I am not concerned whether ROWID is defined in the Create Table or not - my real issue is can I have a ROWID (or other field) with AUTOINC without it being a PRIMARY KEY. Performance will always be an issue, but, if my approach is a problem, imagine what it will be like if the field is not indexed at all! Thanks again ---------------------------------------------------------------------- BigChimp · 2012-04-05 09:12 Mm well... thanks... or not BTW: as you hinted, IMS was not a relational database AFAIR (remember that is, from some classes. Have never seen it). DB2 would be a more appropriate example. This might perhaps influence your thoughts about RDBMS... Also as far as I know, Codd and his friends did always include keys in the discussion of RDBMS, but I wasn't interested in relational database theory at that time so... Personnally, I don't mind if you chose natural keys or artificial ones as keys. I've just found out artificial ones work best for me... and leave it at that. You say meaningless keys limit access points to data. That's not true. If data is not part of a key, you can still look for it using a WHERE clause. The fact that having a good index (unique or otherwise) speeds up this query is an (important) implementation detail. Using unique indexes to restrict your (candidate) natural keys does make sense but I think we agree on that. To spell it out: - a primary key is used when linking another table to it using a foreign key. A side effect is that they are unique. Presumably, most databases (actually all that I know to that level of detail) use a unique index to enforce this. If using a natural key (single or multiple columns/compound) having this key means you enforce uniqueness. - An index is used to speed up access to column data and won't influence the relational model (IIRC). You can also have a unique index... which does limit what data goes where and can be used to enforce your (candidate) natural keys if you want to. You can use a regular index (e.g. ascending, descending) to speed up queries/access to the data, handy if you often search on things like an SSN, Tax ID or surname. Your 1 and 2 are mutually incompatible. A primary key should be unique by itself. But your number 2 by itself would work (from a theoretical perspective). The thing is: ROWID by itself already is unique, so why do you need to add more data to it? What benefit will that bring? Also why do you care whether the key is AUTOINCR or not? My quick glance at the docs I linked to above would indicate not having AUTOINCR would still guarantee unique keys... just not monotonically increasing or whatever but you won't/can't care because your primary key is compound anyway. Regardless of how much I sound like James Martin, I'd suggest you chose either natural keys or artificial ones... not some hybrid. To clear things up, it might also help to discuss what you want to achieve at a higher level.. you mentioned searching for a Tax File Number etc... which is perfectly doable without it being a primary key. (Liking this discussion, it's been a while since I thought about the more theoretical side of RDBMS... and I notice I have learnt some things along the way... probably forgotten a lot as well) Regards, BigChimp ---------------------------------------------------------------------- BigChimp · 2012-04-05 09:15 Quote from: Pascaluvr on April 05, 2012, 11:12:09 amPerformance will always be an issue, but, if my approach is a problem, imagine what it will be like if the field is not indexed at all! Tending to go on a bit but... you can index a column/field without it being a key... ---------------------------------------------------------------------- ludob · 2012-04-05 09:17 QuoteI do agree with the 'meaningless' key idea, but that limits our access points to the data. Why? You can access your data with any field or combination of fields. The primary key is only a way to identify the record in an unique way and, as it is an index, it is a fast way to find the record in the table. If you want the same, fast access by your meaningful key, create an index for that key. Adding indices does not impact your application but makes queries using that index much faster. It makes inserts slower but that is only marginal for most applications. QuoteThat is the main point of my question - can I do this? In my previous post you got the answer: yes it works but it is not the best thing to do. You'll end up with more problems. You are trying to avoid people remembering their Tax File Number but you'll ask them to remember their name (easy for most of us) plus another unique number. Quotemy real issue is can I have a ROWID (or other field) with AUTOINC without it being a PRIMARY KEYNo. AUTOINCREMENT is an attribute of an integer primary key in SQLite. ---------------------------------------------------------------------- Pascaluvr · 2012-04-05 10:01 hi Big Chimp, Sorry if I misled you.  DL1/IMS were nothing like a RDMS (in fact, at the time there was no RDBMS other than on paper), but that was all IBM had at the time, so James Martin would tell us about how wonderful the future will be, and then insist that dl1/ims was the best  thing since sliced bread until we got there.  Basically, he has the highest paid salesman that wasn't in IBM's employ. Now back to my specific question.  We seem to be discussing from 2 different views.  Theoretical and real world. Sure, I know I can search using WHERE to find the occurrences of something, but how long would it take if it wasn't indexed?  Suppose I searched this forum (which I'm sure is a SQL DB) for all posts  by 'Big Chimp' - I can get a response almost immediately, not after searching every post for the occurrence - that's because UserName and (probably) PostNumber are combined to make a Unique Index.  In the wonderful theoretical world of RDBMS keys should be obsolete, we should be able to just search by saying WHERE field = '...' - but in the real world, we can't have the server searching through every row of this forum to find that I have only made a few posts recently and that searching all the previous history is a waste of time and slowing down everyone else's connection.  Keys are a necessary evil. I am not sure why you have a problem with me mixing 'real' and  'created data' to make a unique key?  All that really matters is that I satisfy SQLite by having a unique key, and I can now search using an index to access 'real data'.  Fast response time.  Of course, I have to balance that with the slower INSERT time because of the additional index.  In Database design, the real world and the theoretical world have to be balanced.  Going too far on either side will cause many problems. And yes, I'm enjoying this too.  Its a long time since I have had such a discussion. my very best regards ---------------------------------------------------------------------- Pascaluvr · 2012-04-05 10:04 Quote from: BigChimp on April 05, 2012, 11:15:00 amQuote from: Pascaluvr on April 05, 2012, 11:12:09 amPerformance will always be an issue, but, if my approach is a problem, imagine what it will be like if the field is not indexed at all! Tending to go on a bit but... you can index a column/field without it being a key... Other than saying its UNIQUE, I have no idea how to do that.  If that is the way, then I still need something to make it unique. Am I missing an important point here? ---------------------------------------------------------------------- ludob · 2012-04-05 10:09 QuoteI am not sure why you have a problem with me mixing 'real' and  'created data' to make a unique key?  All that really matters is that I satisfy SQLite by having a unique key, and I can now search using an index to access 'real data'.  Fast response time.If you have an index on multiple fields it is only used if you provide all fields. Searching for BigChimp while having only an index on name+id will be slow. As said before create an index on name if you want to search quickly on name. An index does not have to be unique. See CREATE INDEX syntax here: http://www.sqlite.org/lang_createindex.html A table can have as many indices as you want. ---------------------------------------------------------------------- Pascaluvr · 2012-04-05 10:14 Quote from: ludob on April 05, 2012, 11:17:59 amQuoteI do agree with the 'meaningless' key idea, but that limits our access points to the data. Why? You can access your data with any field or combination of fields. The primary key is only a way to identify the record in an unique way and, as it is an index, it is a fast way to find the record in the table. If you want the same, fast access by your meaningful key, create an index for that key. Adding indices does not impact your application but makes queries using that index much faster. It makes inserts slower but that is only marginal for most applications. Thank you for the reply ludob. Yes I want the same fast access ( and will wear the overhead of the INSERT) , I just want the best way to create a unique index of some data that has duplicate keys.  Perhaps I should just have my own ID_SEQUENCE as a source to make my duplicate keys unique. ---------------------------------------------------------------------- Pascaluvr · 2012-04-05 10:19 Quote from: ludob on April 05, 2012, 12:09:28 pmQuoteI am not sure why you have a problem with me mixing 'real' and  'created data' to make a unique key?  All that really matters is that I satisfy SQLite by having a unique key, and I can now search using an index to access 'real data'.  Fast response time.If you have an index on multiple fields it is only used if you provide all fields. Searching for BigChimp while having only an index on name+id will be slow. As said before create an index on name if you want to search quickly on name. An index does not have to be unique. See CREATE INDEX syntax here: http://www.sqlite.org/lang_createindex.html A table can have as many indices as you want. Thank you lodob ... thank you thank thank you. If you told me before, I must have missed it, so sorry. That answer was exactly what I was looking for ...  no...  it is even better than I expected and just what I need. Thanks again ---------------------------------------------------------------------- BigChimp · 2012-04-05 10:22 I think Ludob already answered a lot. You CANNOT create a unique index on duplicate keys.  It doesn't make sense (theoretically and practically As stated above, you have 2 options:' 1. artificial key; in sqlite something like ID primary key autoincr or however your sample went. Then add unique indexes on any combination of your fields that you think should be unique. 2. natural key: set primary key on one or more fields... these will need to be unique. In both cases, add more indexes if (you think) your queries are slow and search on those fields a lot. You're still trying to do 1 and 2 at the same time. Why? Why do you need to make somethinng that is not unique unique? Perhaps the problem lies more in your table/fields structure than in the primary key question. Once again, showing us what you want to achieve, if possible with real table/column names would help. ---------------------------------------------------------------------- BigChimp · 2012-04-05 10:23 Quote from: Pascaluvr on April 05, 2012, 12:04:09 pmQuote from: BigChimp on April 05, 2012, 11:15:00 amQuote from: Pascaluvr on April 05, 2012, 11:12:09 amPerformance will always be an issue, but, if my approach is a problem, imagine what it will be like if the field is not indexed at all! Tending to go on a bit but... you can index a column/field without it being a key... Other than saying its UNIQUE, I have no idea how to do that.  If that is the way, then I still need something to make it unique. Am I missing an important point here? Yep, I think you were ---------------------------------------------------------------------- Pascaluvr · 2012-04-05 10:28 Quote from: BigChimp on April 05, 2012, 12:23:40 pmQuote from: Pascaluvr on April 05, 2012, 12:04:09 pmQuote from: BigChimp on April 05, 2012, 11:15:00 amQuote from: Pascaluvr on April 05, 2012, 11:12:09 amPerformance will always be an issue, but, if my approach is a problem, imagine what it will be like if the field is not indexed at all! Tending to go on a bit but... you can index a column/field without it being a key... Other than saying its UNIQUE, I have no idea how to do that.  If that is the way, then I still need something to make it unique. Am I missing an important point here? Yep, I think you were yep I was   ludob gave me the answer I needed.  thanks for the perseverance ---------------------------------------------------------------------- BigChimp · 2012-04-05 10:39 No problem, but it might have helped if you said earlier that you wanted to have a key in order to search quickly for that data... (IOW: describe what you wanted at a higher level, instead of how you intend to get to your aim). Still, glad it's solved ---------------------------------------------------------------------- garlar27 · 2012-04-05 12:34 Try to use a GUID as primary key, specially if you don't have a natural key like SSN in a context where SSN is relevant (it's not relevant for an address book but it is for a hospital). Primary keys, are used mainly to link tables and not for searching data. And as a primary key it has many advantages related to it uniqueness. If you have an isolated GUID and searching for it in a huge DB you will find only one origin for it (one item of a sale, one invoice, one client, or one employee but not many of them at the same time like it would happen with an auto incremental key). Beside that it has many performance advantages to do specific queries like find all orders which where canceled, or all discontinued products. ---------------------------------------------------------------------- ludob · 2012-04-05 13:12 A GUID would be just text in sqlite. Having a primary index which is text has a performance hit. Also SQLite doesn't have build in GUID generation code. MSSQL has native support for GUID data types which makes it obviously an easier choice. Other than that I agree with your comments on global unique keys. QuoteBeside that it has many performance advantages to do specific queries like find all orders which where canceled, or all discontinued products. Could you expand a little bit on that?