When creating a new table in a database, what is the importance of using id. For our purposes, we are using unique username and email in each field to match the info with that unique username or email. So what is the use of id?
Also, what is the length/value field for? New to this.
thanks a bunch!
The id field is an example of a surrogate key. It is a good idea to use a surrogate key as a primary key in a database because it is totally unrelated to and therefore unaffected by external events in the real world.
Using a natural key such as the email address could cause problems because if a user changes their email address your key will have to change. This can create difficulties as it will break foreign key contraints. It will also make querying for events relating to a specific user over time more difficult as you have no guaranteed single key that is consistent for that user's entire history.
If you have more than one database in your company that needs the keys, or you export data from your database to other applications or systems then when you change a key in your database you may also need to change the keys in those systems too, something which cannot be done automatically by using ON CASCADE UPDATE.
As others have pointed out, there are two types of keys for records: natural keys and surrogate (artificial) keys. The two major questions, then, are: do you need to use a surrogate key, and if so, what should that surrogate key be?
As to the first question: You only need to use a surrogate key if you have no valid natural key for use as a primary key on the table. All sane database systems support the 'ON UPDATE CASCADE' clause, which means that if you are using a natural key which happens to change, the change will be propagated to everything which is declared to reference it. Of course, if your database system does not support foreign keys, then your best bet is to use a surrogate key, if only to work around the lack of functionality in the database system (and surrogate keys will make your database easier to consistency check in light of that fact). That said, if you are designing an application that has requirements for high uptime and high robustness, select a database implementation that gets foreign keys correct, or you will most likely find that data integrity bugs will be found late in development (or even in maintenance) and you will have to write utilities that will check your data for consistency in various modes of failure.
For the second question: If you use a surrogate key, especially if you are working around a deficiency of a database system, you should always treat it as if it were immutable and globally unique. ALWAYS. This will aid in many situations later on: companies can merge (and split), databases can be merged (and split), and about a million other situations can happen that aren't anticipated when the database is designed that are capable of causing problems if the surrogate keys are not globally unique. Since surrogate keys are not at all related to the data they hold (they have no relation to the other fields in the table other than the artificial one that you have bestowed upon it) it's just best that way. For these reasons, when I must use a surrogate key, I use a UUID (which is essentially a 128-bit integer, but not incremental). Now you don't have to worry about renumbering record numbers and references when unexpected events occur. (Yes, it does slow things down, particularly if your server is running on a 32-bit platform. But if you need to handle more load, distribute the load better---do not sacrifice integrity for speed, ever, when you're working with important data!)
Relations between tables.
Is uneffective have relation to username or email address because this is a string and comparing this values takes much more time, and indexes are bigger, optimal solution is add ID like a primary key for relations to other tables as userid.
Although you want usernames to be unique you should not rely on your database to control uniqueness. It is best practice for your code to test if the username and e-mail already exists in the database.
The purpose of length is for limiting the input of data. For instance varchar with a length of 10 will only allow a 10 character length input. Value is for default purposes. If you insert a new row without declaring this field it will automatically be filled with the value, if set.
The point is to lighten the index. If you don't use an identity field and you choose to use (username, email) as primary key, the search for a user will take some more time looking for the username firt and then the email. Furthermore, these fields are strings, you can't compare the size of an integer and the size of strings like usernames and emails.
And using an identity field will allow you to do things like Comments (id, user_id) and not Comments (id, username, email)...
