When two fields are needed to uniquely identify a record, a ________ key is used.

What Does Primary Key Mean?

A primary key is a special relational database table column (or combination of columns) designated to uniquely identify each table record.

A primary key is used as a unique identifier to quickly parse data within the table. A table cannot have more than one primary key.

A primary key’s main features are:

  • It must contain a unique value for each row of data.
  • It cannot contain null values.
  • Every row must have a primary key value.

A primary key might use one or more fields already present in the underlying data model, or a specific extra field can be created to be the primary key.

Techopedia Explains Primary Key

The primary key concept is critical to an efficient relational database. Without the primary key and closely related foreign key concepts, relational databases would not work.

In fact, since a table can easily contain thousands of records (including duplicates), a primary key is necessary to ensure that a table record can always be uniquely identified.

All keys that come from real-world observables and attributes are called natural primary keys, as opposed to surrogate primary keys that are, instead, arbitrarily assigned to each record.

Almost all individuals deal with natural primary keys frequently but unknowingly in everyday life.

For example, students are routinely assigned unique identification (ID) numbers, and all U.S. citizens have government-assigned and uniquely identifiable Social Security numbers. Street addresses or driver license numbers are examples of primary keys used to uniquely identify (respectively) locations or cars.

As another example, a database must hold all of the data stored by a commercial bank. Two of the database tables include the CUSTOMER_MASTER, which stores basic and static customer data (name, date of birth, address, Social Security number, etc.) and the ACCOUNTS_MASTER, which stores various bank account data (account creation date, account type, withdrawal limits or corresponding account information, etc.).

To uniquely identify customers, a column or combination of columns is selected to guarantee that two customers never have the same unique value. Thus, certain columns are immediately eliminated, e.g., surname and date of birth.

A good primary key candidate is the column that is designated to hold Social Security numbers. However, some account holders may not have Social Security numbers, so this column’s candidacy is eliminated.

The next logical option is to use a combination of columns, such as adding the surname to the date of birth to the email address, resulting in a long and cumbersome primary key.

The best option is to create a separate primary key in a new column named CUSTOMER_ID. Then, the database automatically generates a unique number each time a customer is added, guaranteeing unique identification.

As this key is created, the column is designated as the primary key within the SQL script that creates the table, and all null values are automatically rejected.

The account number associated with each CUSTOMER_ID allows for the secure handling of customer queries and quick search times (as with any indexed table.)

For example, a customer may be asked to provide his surname when conducting a bank query. A common surname (such as Smith) query is likely to return multiple results.

When querying data, utilizing the primary key uniqueness feature guarantees one result.

In relational database management systems, a unique key is a candidate key that is not the primary key of the relation. All the candidate keys of a relation can uniquely identify the records of the relation, but only one of them is used as the primary key of the relation. The remaining candidate keys are called unique keys because they can uniquely identify a record in a relation. Unique keys can consist of multiple columns. Unique keys are also called alternate keys. Unique keys are an alternative to the primary key of the relation. Generally, the unique keys have a UNIQUE constraint assigned to it in order to prevent duplicates (a duplicate entry is not valid in a unique column). Alternate keys may be used like the primary key when doing a single-table select or when filtering in a where clause, but are not typically used to join multiple tables.

Summary[edit]

Keys provide the means for database users and application software to identify, access and update information in a database table. There may be several keys in any given table. For example, in a table of employees, both employee number and login name are individually unique. The enforcement of a key constraint (i.e. a uniqueness constraint) in a table is also a data integrity feature of the database. The DBMS prevents updates that would cause duplicate key values and thereby ensures that tables always comply with the desired rules for uniqueness. Proper selection of keys when designing a database is therefore an important aspect of database integrity.

A relational database table may have one or more available unique keys (formally called candidate keys). One of those keys per table may be designated the primary key; other keys are called alternate keys.

Any key may consist of one or more attributes. For example, a Social Security Number might be a single attribute key for an employee; a combination of flight number and date might be a key consisting of two attributes for a scheduled flight.

There are several types of keys used in database modeling and implementations.

Key Name Definition
Simple A key made from only one attribute.
Concatenated A key made from more than one attribute joined together as a single key, such as part or whole name with a system generated number appended as often used for E-mail addresses.
Compound A key made from at least two attributes or simple keys, only simple keys exist in a compound key.
Composite Like a compound key, but the individual attributes need not be simple keys.
Natural A key made from data that exists outside the current database. In other words, the data is not system generated, such as a social security number imported from another system.
Surrogate An artificial key made from data that is system assigned or generated when another candidate key exists. Surrogate keys are usually numeric ID values and often used for performance reasons.[citation needed]
Candidate A key that may become the primary key.
Primary The key that is selected as the primary key. Only one key within an entity is selected to be the primary key. This is the key that is allowed to migrate to other entities to define the relationships that exist among the entities. When the data model is instantiated into a physical database, it is the key that the system uses the most when accessing the table, or joining the tables together when selecting data.
Alternate A non-primary key that can be used to identify only one row in a table. Alternate keys may be used like a primary key in a single-table select.
Foreign A key that has migrated to another entity.

At the most basic definition, "a key is a unique identifier",[1] so unique key is a pleonasm. Keys that are within their originating entity are unique within that entity. Keys that migrate to another entity may or may not be unique, depending on the design and how they are used in the other table. Foreign keys may be the primary key in another table; for example a PersonID may become the EmployeeID in the Employee table. In this case, the EmployeeID is both a foreign key and the unique primary key, meaning that the tables have a 1:1 relationship. In the case where the person entity contained the biological father ID, the father ID would not be expected to be unique because a father may have more than one child.

Here is an example of a primary key becoming a foreign key on a related table. ID migrates from the Author table to the Book table.

Author Table Schema:

Author(ID, Name, Address, Born)

Book Table Schema:

Book(ISBN, AuthorID, Title, Publisher, Price)

Here ID serves as the primary key in the table 'Author', but also as AuthorID serves as a Foreign Key in the table 'Book'. The Foreign Key serves as the link, and therefore the connection, between the two related tables in this sample database.

In a relational database, a candidate key uniquely identifies each row of data values in a database table. A candidate key comprises a single column or a set of columns in a single database table. No two distinct rows or data records in a database table can have the same data value (or combination of data values) in those candidate key columns since NULL values are not used. Depending on its design, a database table may have many candidate keys but at most one candidate key may be distinguished as the primary key.

A key constraint applies to the set of tuples in a table at any given point in time. A key is not necessarily a unique identifier across the population of all possible instances of tuples that could be stored in a table but it does imply a data integrity rule that duplicates should not be allowed in the database table. Some possible examples of keys are Social Security Numbers, ISBNs, vehicle registration numbers or user login names.

In principle any key may be referenced by foreign keys. Some SQL DBMSs only allow a foreign key constraint against a primary key but most systems will allow a foreign key constraint to reference any key of a table.

Defining keys in SQL[edit]

The definition of keys in SQL:

  ALTER TABLE <table identifier> 
      ADD [ CONSTRAINT <constraint identifier> ] 
      { PRIMARY KEY | UNIQUE } ( <column name> [ {, <column name>}... ] )

Likewise, keys can be defined as part of the CREATE TABLE SQL statement.

  CREATE TABLE table_name (
     id_col   INT,
     col2     CHARACTER VARYING(20),
     key_col  SMALLINT NOT NULL,
     ...
     CONSTRAINT key_unique UNIQUE(key_col),
     ...
  )

  CREATE TABLE table_name (
     id_col  INT  PRIMARY KEY,
     col2    CHARACTER VARYING(20),
     ...
     key_col  SMALLINT NOT NULL UNIQUE,
     ...
  )

Differences between Primary Key constraint and Unique constraint[edit]

Primary Key constraint

  1. A primary key cannot allow null (a primary key cannot be defined on columns that allow nulls).
  2. Each table cannot have more than one primary key.
  3. On some RDBMS a primary key generates a clustered index by default.

Unique constraint

  1. A unique constraint can be defined on columns that allow nulls, in which case rows that include nulls may not actually be unique across the set of columns defined by the constraint.
  2. Each table can have multiple unique constraints.
  3. On some RDBMS a unique constraint generates a nonclustered index by default.

Note that unlike the PRIMARY KEY constraint a UNIQUE constraint does not imply NOT NULL for the columns participating in the constraint. NOT NULL must be specified to make the column(s) a key. It is possible to put UNIQUE constraints on nullable columns but the SQL standard states that the constraint does not guarantee uniqueness of nullable columns (uniqueness is not enforced for rows where any of the columns contains a null).

According to the SQL[2] standard a unique constraint does not enforce uniqueness in the presence of nulls and can therefore contain several rows with identical combinations of nulls and non-null values — however not all RDBMS implement this feature according to the SQL standard.[3][4]

See also[edit]

  • Globally Unique Identifier
  • Persistent Object Identifier

References[edit]

  1. ^ Awad, Elias (1985), Systems Analysis and Design, Second Edition, Richard D. Irwin, Inc., ISBN 0-256-02824-9
  2. ^ Summary of ANSI/ISO/IEC SQL Archived April 25, 2012, at the Wayback Machine
  3. ^ "Constraints - SQL Database Reference Material - Learn sql, read an sql manual, follow an sql tutorial, or learn how to structure an SQL query!". www.sql.org. Retrieved 16 August 2018.
  4. ^ "Comparison of different SQL implementations". troels.arvin.dk. Retrieved 16 August 2018.

  • Relation Database terms of reference, Keys: An overview of the different types of keys in an RDBMS

What key refers to the unique field that identifies each record?

Primary Key - a field containing a value that uniquely identifies each record in a table. The primary key is unique and prevents entering duplicate records or a null value in that field.

Is a field or set of fields that uniquely identifies a database record?

Primary key A table can have only one primary key. A primary key consists of one or more fields that uniquely identify each record that you store in the table.

What is a primary key quizlet?

Primary key. A field, or collection of fields, whose values uniquely identify each record in a table (different ID numbers) - used to identify each record because there can't be more than one. Foreign key. A primary key field that relates a record to another record.

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.