Without primary keys, things can get really messy, really fast. But it's not working, it's returning the error 'Column count doesn't match value count at row 1'. INSERT with an ON DUPLICATE KEY UPDATE clause enables existing rows to be updated if a row to be inserted would cause a duplicate value in a UNIQUE index or PRIMARY KEY. By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1. Oh, and there're 39 unique passwords for "bob" (157 of the rows all show the password "password", of course). I'm trying to use an INSERT INTO statement without having to entering the primary key - apparently I shouldn't need to, it should update itself. MySQL uses the AUTOINCREMENT keyword to perform an auto-increment feature. And, if you've tried to connect rows in some other table to user without a primary key/foreign key relationship - every row in message marked as sent_by "bob" ties back to every one of those 300 user entries. If you try to insert a value of NULL or 0 or DEFAULT, or if you omit the auto-increment column from the columns in your INSERT statement, this activates the auto-increment generator. This is fine it simply overrides the auto-increment generator. What you do have is a user table with 300 separate entries for a user named "bob", many of which have different values in the other columns. You can insert into an auto-increment column and specify a value. It's true, without a primary key (or some unique key), you don't have an insertion anomaly if you go to insert the same data multiple times. It makes it much easier to maintain the data. Should you create a database table without a primary key? No.Įvery table should have some column (or set of columns) that uniquely identifies one and only one row. And, I believe that holds true for almost every (if not every) major DBMS platform. Can you create a database table without a primary key? Well, you just said you can in SQLite. This tutorial shows you how to use the MySQL INSERT IGNORE statement to insert rows with valid data into a table while ignoring rows that cause errors.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |