I need help locking records.

[Banana]

It won't let me define both of them as unique.

Probably because they aren't. If they are one field, they are unique, but as two fields, it is possible to have duplicates. But I don't want any where both fields are identical, which would indicate a duplicate account.

I'm confused. By definition, a primary key has no duplicates, and therefore there shouldn't be any problem changing a primary key to UNIQUE index. In fact primary key basically means no-nulls-allowed UNIQUE index.

But if the pair of columns could have duplicate (aggregated) value, then it couldn't very well be a primary key in first place.

Wait, did you try to define them as UNIQUE individually? Is that what you mean? The composite UNIQUE index should look like this:

(from Indexes dialog that pop up when you click on Indexes button in table design view)

Note that the second column (from_date) participating doesn't have a index name- it's blank and thus is included in the the "ExampleCompositeIndex"

But I usually do it via DDL:

CREATE UNIQUE INDEX ExampleCompositeIndex ON dept_manager (to_date, from_date);

 

[odin1701]

I didn't know you could do that.

The two fields, together, make a PK. By themselves they are not unique.

Anyway it's working now.

 

[Banana]

Yes, I now realize it's actually easier to make composite primary key than it is to make composite unique index and to be honest, the method for making unique index via UI is kind of confusing. That's why I usually use DDL.

Glad you're sorted now.