Is this possible?

[allboutdB]

Hi there,

I have a table, where the PK is an autonumber, but i also want to put a unique constrain on a combination of two columns to ensure that someone doesn't input the value(combination of the two columns) that all ready exist in the table , is this possible in access?

Cheers

 

[Banana]

If those two columns are foreign keys to other tables, then you don't actually need a autonumber. Just select both columns and make it a composite key. This will then ensure no duplicate for any possible combination.

If they aren't foreign keys (and I should note that I can't think of any scenario where they shouldn't; 99% of the time, they should be a foreign key in that context), I believe you still can create a composite index, by opening Properties windows and creating an index that touches both column and setting it to No Duplicates.

Hope that helps.

 

[allboutdB]

Actually i have 3 columns in this table, i can not make a natural PK in this table as it would have to be a composite PK made up of all 3 coloumns, the problem is in some records the value in the 3rd column is null, and can't use a combination of the 1st two columns as they are repeats in few records,so i chose the autonumber as PK, and i tried doing what you just suggested, and it didn't work. Access let me create a unique index on individual columns but not on a combination of columns

Regards

 

[Banana]

The composite index should be created in a separate dialog windows, not on table design view which manages only one column at a time.

I don't know what version you have, but just drop the SQL in query and execute it, and you'll get what you need. Just substitute the names where needed:

CREATE UNIQUE INDEX ON <yourtablename> (<firstcolumnname>, <secondcolumnname>, <thirdcolumnname>);

Be aware, though, that UNIQUE index will not discriminate one null from other null so if you have a entry with two columns filled, one null in third, it won't prevent a new entry with same combination being added, so you may want to consider disabling nulls and using a placeholder or maybe zero-length string.

 

[gemma-the-husky]

query?

table design indexes are/can be multi column

just select indexes icon, and design away - unique option at the bottom

 

[allboutdB]

Tried doing that,

and it doesn't work, i have unique index on the combination of the 2 columns and i tried keying same data in the first column and different data in the second column, but it gave me errors

 

[allboutdB]

Sorry guys,

my mistake, it worked both way , i was getting that error as i had not removed the old indexes.

Cheers , much appreciate your help