SQL Query

JohnnyBravo

Say i have a table lets call it Test it has two fields prefex and number
I want to insert new values into the table
However i want to check that a phone number that i enter isnt already in there for example same prefex and same Phone number
Phone number with different prefexs and same numbers are allowed
Can you do this in SQL and if so how

stas

Just create a primary key on both fields:

create table test1 (
prefix varchar2(5) not null,
num varchar2(15) not null,
primary key (prefix, num)
);

-- these two will work
insert into test1 (prefix, num) values ('10', '2000');
insert into test1 (prefix, num) values ('10', '2001');

-- this will trigger a constraint violation
insert into test1 (prefix, num) values ('10', '2000');

JohnnyBravo

The table is in access
And it is set up so that there is multiple prefex codes in the table


Ie


prefex number
1 1
1 2
1 3
1
1
1

stas

I have little experience with Access sorry. Does it not have support for combined primary keys?

JohnnyBravo

cououldnt tell you im using a reporting tool that uses odbc to gert into it

amen

How about if Exists
ie.e
IF NOT EXISTS( SELECT Number From Test
WHERE Number = @Number
AND Prefix =@Prefix
)
BEGIN
Do your insert
END

stas

Well anyhow, you only have two options really:
1. Alter the actual table to add the combined primary key - which is the only normal way to go about this problem (although this has to be supported by Access)
2. Prior to every insert try to select the same data from the table and see if there were any rows returned - OUCH!

stevenmu

Access should support combined keys, maybe some older versions don't but 2000 does. Just go to the table design view, drag your mouse on the fields to select them all, right click and choose 'Set Primary Key'.

HaVoC

Go into access in design view of the tables, Select the field, in the general tab at the bottom goto indexed and select Yes(No duplicates) or select one field hold down shift and selecet the other field still holding shift right click and select primary key both will trow and exception as in stas's solution