Adaikalavan Ramasamy <[EMAIL PROTECTED]> wrote on 26/07/2004
16:05:23:
> I am creating a small database that keeps track of users and assigns
> them a unique user ID.
>
> The problem is that sometimes the users might request to be added more
> than once (i.e. click on the submit button multiple times). Therefore I
> only want to add users if their details (here defined by both firstname,
> lastname) are not in the database. Example :
>
> CREATE TABLE tb ( myID INT NOT NULL auto_increment, firstname
> VARCHAR(10), lastname VARCHAR(10), PRIMARY KEY(myID) );
> INSERT INTO tb (firstname, lastname) VALUES ('John', 'Doe');
> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe');
> INSERT INTO tb (firstname, lastname) VALUES ('Paul', 'Smith');
> SELECT * from tb;
> +------+-----------+----------+
> | myID | firstname | lastname |
> +------+-----------+----------+
> | 1 | John | Doe |
> | 2 | Jack | Doe |
> | 3 | John | Smith |
> +------+-----------+----------+
>
> I get syntax error with the following :
>
> INSERT INTO tb (firstname, lastname) VALUES ('Jack', 'Doe') IF NOT
> EXISTS ( SELECT * FROM tb WHERE firstname='Jack' AND lastname='Doe' );
>
> In this case, I want no insert because Jack Doe already exists. Can
> anyone help me ?
Simply create a UNIQUE index on the fields which you with to be unique.
Add into yoyr table cration the line
UNIQUE (firstname, lastname),
MySQL will then reject any attempt to make that combination non-unique.
Alec
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
