A key uniquely identifies each row in a table. Show
When you add a row to a table, that row must have a unique key value. The row's key value must remain constant for the life of the row. When you update or delete a row through an AppSheet app, the key is used to find the row to update or delete. If two or more rows are inadvertently assigned the same key value, we say the table contains "duplicate keys". This is a serious problem. If someone attempts to update or delete one of these rows, the wrong row may be updated or deleted. Above, we can see that the email address is selected as the key of the table. In this case, the key is the email address because the name uniquely identifies each user. Therefore, there is no risk of duplicate keys and the resulting issues. The following sections provide more information about keys:
Types of keysAppSheet supports three types of keys. From best to worst, these key types are:
Natural keysMany tables have a Natural key, a single field or a combination of fields that uniquely identify each table row. Natural single-column keysYour table may often have a single field containing a value that uniquely identifies each row. For example, an Natural multi-column keysYour table may contain two or more fields that together contain values uniquely identifying each row. For example, a Natural computed keysYour table may contain fields that can be combined to yield a unique computed key value. The row's computed key value must remain constant for the life of the row. You specify the key computation expression in the key field's app formula property. The editor examines this expression to ensure that it yields the same result over time, and displays an error if it does not. For example, an app formula that includes the current date or time might yield different results over time, so it would be prohibited in a key field's app formula. Why not worksheet formulas?In a spreadsheet, it's sometimes convenient to have an ID column that is computed with a worksheet formula. For example, the worksheet formula might increment the value in the previous row. This does not work for AppSheet table keys for two reasons. First, keys must be unique and unchanging over time, but that is not possible to guarantee with worksheet formulas. Second, it must be possible to compute the formula when the app is working offline, but that is not possible with worksheet formulas. System-generated keysSome tables do not have a natural key. Instead, you would like the system to generate a unique key for each new row. Sequential keysWe are
often asked if there is a way for AppSheet to generate sequential, unique keys starting from a user specified initial value. Sequential means there must be no gaps in the key sequence. For example, the values Unfortunately in a distributed system, with multiple users, and offering offline data inserts, it's technically impossible to generate identifiers satisfying all of these requirements. That is true whether the generated value is used as a key value or as a normal field value. You can use a Random keysOne alternative to system-generated sequential keys, is system-generated random keys. AppSheet currently supports two mechanisms for creating system-generated random keys.
Because these functions generate pseudo random values, they do not require coordination between the clients and the server. They work for multiple users doing offline inserts. Row number keysIf you do not specify a key, and AppSheet cannot automatically find a good key, AppSheet will default to using the worksheet row number as the key. The row number is not a good key. If entries are moved or deleted, or if users add or delete entries simultaneously, the row number for each row will change and there is no way for AppSheet to uniquely identify the row. The Editor will give a warning if row number is chosen as the key. Select a keyKey selection can be automatic or manual. Automatic key selectionWhen you add a new table to you app, AppSheet attempts to find a suitable key for the table. AppSheet first examines each worksheet column from left to right, looking for a column that contains unique data values. If your worksheet has a good key column, it's a good practice to make it the leftmost worksheet column. If AppSheet finds such a column, it makes that column the key. Appsheet next examines pairs of worksheet columns from left to right, looking for a pair of columns that contain unique data values. The pair of columns need not be adjacent. If AppSheet finds such a pair of columns, it will combine the columns to create a computed key. The computed key will be added as a Virtual column at the end of the table. If AppSheet cannot find a key column using any of these techniques, it will default to using row number as the key. The editor will give a warning if row number is chosen as the key. Manual key selectionYou can manually override AppSheet's key selection as follows:
The key value for a record must be assigned once when the record is created and it must remain constant for the life of the record. That is why you must specify the key value in the Initial Value property and never in the App Formula property. The Initial Value property is computed once when the record is created. By contrast, the App Formula is computed once when the record is created and is recomputed each time the record is updated. Column types that can't be used as the keyThe following column types can't be used as the key in your table:
What is a unique field called in a database quizlet?Each record in the database must have a unique identifier called a primary key. No two records in the database table can have the same primary key value. The primary key value cannot be null (empty). Each record must have a value for the primary key field.
What must be unique about each field in a table?Each field in a table must have a unique name. You can sort a datasheet by one field, or a set of adjacent fields, in ascending or descending order.
What field contains a unique value for each record?A primary key consists of one or more fields that uniquely identify each record that you store in the table.
Which data type assigns a unique value to each record in a table?A primary key is a special relational database table column (or combination of columns) designated to uniquely identify each table record.
|