Here are a few examples of fields that would make poor primary keys for a Contacts table, along with reasons why they would be poor choices. The fact portion might change, creating a maintenance burden. Could lead to confusion if the fact portion is repeated as a separate field. For example, combining the city and an incremented number e. In some cases, you want to use two or more fields in a table as the primary key. A key that has more than one field is called a composite key. For a primary key to work well, the field must uniquely identify each row, never contain an empty or null value, and rarely ideally, never change.
To set the primary key:. In the Navigation Pane, right click the table in which you want to set the primary key and, on the shortcut menu, click Design View. Tip: If you don't see the Navigation Pane, press F11 to display it. To select more than one field to create a composite key, hold down CTRL and then click the row selector for each field. On the Design tab, in the Tools group, click Primary Key.
When you remove the primary key, the field or fields that previously served as primary key will no longer provide the primary means of identifying a record. Before you can remove a primary key, you must make sure that it doesn't participate in any table relationships.
If you try to remove a primary key that is part of one or more relationships, Access warns you that you must delete the relationships first. To delete a table relationship, complete the following steps:. If the tables that participate in the table relationship are open, close them. You cannot delete a table relationship between open tables. On the Database Tools tab, in the Relationships group, click Relationships.
Click the table relationship line for the table relationship that you want to delete the line becomes bold when it is selected , and then press the DELETE key. On the Design tab, in the Relationships group, click Close.
After you delete the relationships, in the Navigation Pane, right click the table from which you want to remove the primary key and then click Design View. If the primary key consists of multiple fields, click the row selector for any field in the primary key.
The key indicator is removed from the field or fields that you previously specified as the primary key. Note: When you save a new table without setting a primary key, Access prompts you to create one. If you choose Yes , Access creates an ID field that uses the AutoNumber data type to provide a unique value for each record. If your table already includes an AutoNumber field, Access uses that field as the primary key.
Remove the existing primary key using the instructions in the section Remove the primary key. Set the primary key using the instructions in the section Set the primary key. For more information about database design and choosing an appropriate primary key, see the articles:.
Database basics. Learn the structure of an Access database. Create an Access database. Create a table. Creating tables. Need more help? Expand your skills. Get new features first. A subscription to make the most of your time. Try one month free. Was this information helpful? Yes No. Thank you! Any more feedback? The more you tell us the more we can help. I read that, amongst other things, Access does not allow a table to have a primary key with a null value.
Null values aside: Access does appear to allow a leading tab character within the primary key … whilst I'm far from expert with database technologies, this strikes me as potentially troublesome. What's the best practice for primary keys in tables? Microsoft's glossary for Access states, in relation to standard naming conventions :. As far as the relational model is concerned, all candidate keys are created equal.
There's no formal, logical principle that you can apply to a table having two candidate keys that lets you say, " This one is the only one that can be the primary key. Somebody has to type in all the useful data in a database. There are well known, well supported ways of dealing with the propagation of typographic errors, the most common of which is to use on update cascade in foreign key references.
SQL databases give you a lot of control over what a column allows. In Access, use a validation rule on a column. Stack Overflow for Teams — Collaborate and share knowledge with a private group. Create a free Team What is Teams? Collectives on Stack Overflow. Learn more. For a primary key value in a Microsoft Access database, should a leading control character be prevented? Ask Question. Asked 7 years, 3 months ago. Active 7 years, 3 months ago.
Viewed times. Provided the primary key PK value is unique within a table: is it essential or advisable to prevent or discourage leading control characters — such as tab characters? Related What's the best practice for primary keys in tables? Improve this question. Community Bot 1 1 1 silver badge. Graham Perrin Graham Perrin 1 1 gold badge 10 10 silver badges 43 43 bronze badges.
0コメント