Primary Key Uniqueness

[Ad]

When I select a fieldname in a db as a primary key this will keep the uniqueness of that fieldname, but how is that work when I select 2 fieldnames (in Enterprise Manager) and click on the Key it will mark them as my primary key, but I can insert duplicates values in either fieldname.

Can someone explain this to me.

Thanks

[Kristine Greenlee]

When you setup a primary key using more than one field (a composite key), SQL Server preserves the uniqueness of the combination of the fields, not each field individually.

Field1 Field2
a 1
a 2
b 1
b 2

If you want uniqueness on each field individually, consider creating a UNIQUE index on the field itself.

CREATE UNIQUE INDEX I_AK1_tablename ON tablename(Field1)

------------
Ad at 9/21/00 1:42:35 PM


When I select a fieldname in a db as a primary key this will keep the uniqueness of that fieldname, but how is that work when I select 2 fieldnames (in Enterprise Manager) and click on the Key it will mark them as my primary key, but I can insert duplicates values in either fieldname.

Can someone explain this to me.

Thanks

[David Westmore]

If I understand your message correctly, you have defined two different fields as your primary key.

This means that the COMBINATION of the values of the two fields must be unique. It DOES NOT mean that there cannot be duplicate values in the single fields.

For example, suppose that FLD1 and FLD2 make up your primary key.

SQL would accept three records with these values:

FLD1="AAAA", FLD2 = "12345"
FLD1="CCCC", FLD2 = "12345"
FLD1="AAAA", FLD2 = "45678"

Note that "AAAA" occurs twice in FLD1 and "12345" twice in FLD2

However, if you tried inserting

FLD1="AAAA", FLD2 = "12345"

It would not accept the record, because an identical combination already exists.

Now, if what you meant to do was to have two fields which BOTH must have unique values, only one of these fields can be the primary key (it probably doesn't matter which of the two fields you choose). The other field needs to be associated with a secondary index which will have the UNIQUE attribute set.

To define secondary indexes, open the table in Design view and click on the properties button on the toolbar. In the dialog box, select the indexes tag.

Good luck

David Westmore

It would not accept







------------
Ad at 9/21/00 1:42:35 PM


When I select a fieldname in a db as a primary key this will keep the uniqueness of that fieldname, but how is that work when I select 2 fieldnames (in Enterprise Manager) and click on the Key it will mark them as my primary key, but I can insert duplicates values in either fieldname.

Can someone explain this to me.

Thanks