insert into issue

[playwright]

Hello.I have created a table with some rows. Then, i want to add more rows in the table using insert into. However, i need to check if any of the rows that i add is ALMOST identical to a row that already exists, so as to avoid adding rows with the same content in the same table.

To be more specific i have a table like this one:

First column: id (primary key), Second column:name, Third column:number

(1,'John','123')

(2,'John','123')

(3,'Bill','345')

I dont want to enter both first and second entry since they have the same name and number. Should i first enter them and then delete the one?

Any ideas???

[Pikachu2000]

You shouldn't be specifying the value for the primary key field; it should be an auto-incremented field. If you don't want duplicate fields, you need to enforce unique indexes on those fields. Then you can use INSERT . . . ON DUPLICATE KEY UPDATE or INSERT IGNORE syntax to do your inserts.

[playwright]

You mean that i should remove primary key index from the id field?? i understand what you say about unique indexes. However,i want to be able to enter rows like this one:

(id, 'John','567')

if there is already an entry like :

(id, 'John', '123') or (id,'Bill','567')

So i cant use unique index.

Actually i shouldn't have entries that have identical name and number with another row. However, the entire row won't be identical since id is different.

 

[Pikachu2000]

No, I'm saying don't insert your own value into a primary key id field. You set it as auto increment and let it insert the next sequential value on its own.

 

As far as your other question, your initial example is different from what you describe. What is it you're trying to do? Knowing that might make a solution easier to come up with.

[playwright]

I have a table (id,name,number). Id is auto-increment so it is different in every row. I want to add more entries in my table. However, when i add entries i don't want to insert rows that name AND number that are identical to another row that already exists.

For example if there is already a row in my table (4,'Bill', '345'), I want to be able to insert (NULL,'Bill','667') or

(NULL,'John','345') but not (NULL,'Bill', '345') (i use NULL since it is auto-increment)

Is it appropriate to use unique (name,number)?

[fenway]

Yes.