Primary Keys vs Foreign Keys: For upon|The PRIMARY KEY constraint is used to uniquely identify every record in a table. The specification of a primary key ensures that there are no duplicate values in a column. Additionally, primary key fields are stored in ascending order and default to NOT NULL.

In the Create Toys script, the ToyID column contains a PRIMARY KEY constraint. The CONSTRAINT and PRIMARY KEY keywords are used to define the primary key. The name of the constraint (ToyPk) follows the CONSTRAINT keyword. Primary keys can also be defined using only the PRIMARY KEY keywords; however, this method does not enable you to assign a name to your primary key constraint. Assigning a name to your PRIMARY KEY constraint is vital because it makes it easier for you to update the constraint if necessary. To view the new Toys table, type the following script:

SELECT *
FROM Toys;

This script uses a SELECT statement to retrieve records from a table. The SELECT keyword combined with an asterisk (*) instructs Microsoft Access to retrieve all the columns from a table. The FROM keyword instructs Microsoft Access to retrieve the records from the Toys table. Primary Keys vs Foreign Keys.

Example-C

Exploring Moritz Forster Heise Online: A Comprehensive…

Understanding the CZ Scale CAS: Features, Applications, and…

Comprehensive Guide to Transformer Electric Service Downers…

You want to link the Toys table in Example-B to a new table named Manufacturers. Additionally, you want to ensure that all phone numbers entered into the PhoneNumber column in the Manufacturers table are unique and that all updates and deletions made to the Manufacturers table affect corresponding records in the Toys table. Take a look at the following script:

    CREATE TABLE Manufacturers
    (
    ManufacturerID INTEGER CONSTRAINT ManfID PRIMARY KEY,
    ToyID INTEGER NOT NULL,
    CompanyName CHAR (50) NOT NULL,
    Address CHAR (50) NOT NULL,
    City CHAR (20) NOT NULL,
    State CHAR (2) NOT NULL,
    PostalCode CHAR (5) NOT NULL,
    AreaCode CHAR (3) NOT NULL,
    PhoneNumber CHAR (8) NOT NULL UNIQUE,
    CONSTRAINT ToyFk FOREIGN KEY (ToyID) REFERENCES Toys (ToyID)
    ON UPDATE CASCADE
    ON DELETE CASCADE
    );

Note: Make sure you have created the Toys table in Example-B (in the previous section) before you create the Manufacturers table since it contains the foreign key reference to the Toy table.

The preceding SQL script creates a table named Manufacturers with nine columns (ManufacturerID, ToyID, CompanyName, Address, City, State, PostalCode, AreaCode, PhoneNumber). A PRIMARY KEY constraint is defined for the ManufacturerID column, and the NOT NULL constraint is defined for all other columns. The PhoneNumber column contains a UNIQUE constraint and the ToyID column contains a FOREIGN KEY constraint. Following is an explanation of the ON UPDATE CASCADE and ON DELETE CASCADE keywords and the UNIQUE and FOREIGN KEY constraints used in Example-C. Primary Keys vs Foreign Keys.

The article was originally published here.

Primary Key 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’s 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.). Primary Keys vs Foreign Keys.

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.

The article was originally published here.

Primary and Foreign Keys – Database Primary and Foreign Keys:

1Primary and foreign keys are the most basic components on which relational database theory is based. 2Primary keys enforce entity integrity by uniquely identifying entity instances. Foreign keys enforce referential integrity by completing an association between two entities. The next step in building the basic data model is to:

Identify and define the primary key attributes of each entity
Validate primary keys and relationships
Migrate the primary keys to establish foreign keys

Define Primary Key Attributes:

Attributes are data items that describe an entity. An attribute instance is a single value of an attribute for an instance of an entity. For example, Name and hire date are attributes of the entity EMPLOYEE. “Robert Thompson” and “12 April 1999” are instances of the attribute’s name and hire date.

The primary key is an attribute or a set of attributes that uniquely identify a specific instance of an entity. Every entity in the data model must have a primary key whose values uniquely identify instances of the entity.

To qualify as a primary key for an entity, an attribute must have the following properties:

It must have a non-null value for each instance of the entity
The value must be unique for each instance of an entity
The values must not change or become null during the life of each entity instance

In some instances, an entity will have more than one attribute that can serve as a primary key. Any key or minimum set of keys that could be a primary key is called a candidate key. Once candidate keys are identified, choose one, and only one, the primary key for each entity. Choose the identifier most commonly used by the user as long as it conforms to the properties listed above. Candidate keys that are not chosen as the primary key are known as alternate keys.

An example of an entity that could have several possible primary keys is Employee. Let’s assume that for each employee in an organization, there are three candidate keys: Employee ID, Social Security Number, and Name.

Name is the least desirable candidate. While it might work for a small department where it would be unlikely that two people would have exactly the same name, it would not work for a large organization that had hundreds or thousands of employees. However, there is the possibility that an employee’s name could change because of marriage (your primary key should never be changed). Employee ID would be a good candidate as long as each employee was assigned a unique identifier at the time of hire. Social Security would work best since every employee is required to have one before being hired. Primary Keys vs Foreign Keys.

Composite Keys:

Sometimes it requires more than one attribute to uniquely identify an entity. A primary key that is made up of more than one attribute is known as a composite key. Below shows an example of a composite key. Each instance of the entity Work can be uniquely identified only by a composite key composed of Employee ID and Project ID.

**Example of Composite Keys**
EmployeeID	ProjectID	hours worked
01	01	100
01	02	120
02	01	75
02	03	115
03	03	140
03	04	80

Artificial Keys:

An artificial key is one that has no meaning to the business or organization. Artificial keys are permitted when:

no attribute has all the primary key properties, or
the primary key is large and complex

Primary Key Migration:

Dependent entities, entities that depend on the existence of another entity for their identification, inherit the entire primary key from the parent entity. Every entity within a generalization hierarchy inherits the primary key of the root generic entity.

Define Key Attributes:

Once the keys have been identified for the model, it is time to name and define the attributes that have been used as keys.

There is no standard method for representing primary keys in ER diagrams. For this article, the name of the primary key followed by the notation (PK) is written inside the entity box. An example is shown below:

Entities with Key Attributes

Validate Keys and Relationships

The basic rules governing the identification and migration of primary keys are:

Every entity in the data model shall have a primary key whose values uniquely identify entity instances.
The primary key attribute cannot be optional (i.e., have null values).
The primary key cannot have repeating values. That is, the attribute may not have more than one value at a time for a given entity instance is prohibited. This is known as the No Repeat Rule.
Entities with compound primary keys cannot be split into multiple entities with simpler primary keys. This is called the Smallest Key Rule.
Two entities may not have identical primary keys with the exception of entities within generalization hierarchies.
The entire primary key must migrate from parent entities to child entities and from supertype, and generic entities, to subtypes, and category entities.

Foreign Keys:

A foreign key is an attribute that completes a relationship by identifying the parent entity. Foreign keys provide a method for maintaining integrity in the data (called referential integrity) and for navigating between different instances of an entity. Every relationship in the model must be supported by a foreign key.

Identifying Foreign Keys:

Every dependent and category (subtype) entity in the model must have a foreign key for each relationship in which it participates. Foreign keys are formed independent and subtype entities by migrating the entire primary key from the parent or generic entity. If the primary key is composite, it may not be split.

Foreign Key Ownership:

Foreign key attributes are not considered to be owned by the entities to which they migrate, because they are reflections of attributes in the parent entities. Thus, each attribute in an entity is either owned by that entity or belongs to a foreign key in that entity.

If the primary key of a child entity contains all the attributes in a foreign key, the child entity is said to be “identifier dependent” on the parent entity, and the relationship is called an “identifying relationship.” If any attributes in a foreign key do not belong to the child’s primary key, the child is not identifier dependent on the parent, and the relationship is called “non-identifying.”

Diagramming Foreign Keys:

Foreign key attributes are indicated by the notation (FK) beside them. An example is shown above.

Summary:

Primary and foreign keys are the most basic components on which relational theory is based. Each entity must have an attribute or attribute, the primary key, whose values uniquely identify each instance of the entity. Every child entity must have an attribute, the foreign key, that completes the association with the parent entity.

The article was originally published here.

Types Of Keys In Database

Introduction about Key

A Key is a data item that exclusively identifies a record. In other words, a key is a set of column(s) that is used to uniquely identify the record in a table. It is used to fetch or retrieve records/data rows from the data table according to the condition/requirement. Key provides several types of constraints like a column can’t store duplicate values or null values. Keys are also used to generate relationships among different database tables or views.

Types of Keys
The database supports the following types of keys.

Super Key
Minimal Super Key
Candidate Key
Primary Key
Unique Key
Alternate Key
Composite Key
Foreign Key
Natural Key
Surrogate Key

Now we take two tables for a better understanding of the key. The first table is “Branch Info” and the second table is “Student_Information”.

Now we read about each key.

Candidate Key
A Candidate key is an attribute or set of attributes that uniquely identifies a record. Among the set of candidates, one candidate key is chosen as the Primary Key. So a table can have multiple candidate keys but each table can have a maximum of one primary key.

Example:
Possible Candidate Keys in Branch_Info table.

Branch_Id
Branch_Name
Branch_Code

Possible Candidate keys in the Student_Information table.

Student_Id
College_Id
Rtu_Roll_No

Primary Key

A Primary key uniquely identifies each record in a table and must never be the same for the 2 records. The primary key is a set of one or more fields ( columns) of a table that uniquely identify a record in the database table. A table can have only one primary key and one candidate key can select as a primary key. The primary key should be chosen such that its attributes are never or rarely changed, for example, we can’t select the Student_Id field as a primary key because in some cases Student_Id of a student may be changed.

Example:
Primary Key in Branch_Info table:

Branch_Id

Primary Key in Student_Information Table:

College_Id

Alternate Key:
Alternate keys are candidate keys that are not selected as the primary keys. The alternate key can also work as a primary key. The alternate key is also called the “Secondary Key”.

Example:
Alternate Key in Branch_Info table:

Branch_Name
Branch_Code

Alternate Key in Student_Information table:

Student_Id
Rtu_Roll_No

Unique Key:
A unique key is a set of one or more attributes that can be used to uniquely identify the records in the table. The unique key is similar to the primary key but the unique key field can contain a “Null” value but the primary key doesn’t allow a “Null” value. Another difference is that the primary key field contains a clustered index and the unique field contains a non-clustered index.

Example:
Possible Unique Key in Branch_Info table.

Branch_Name

Possible Unique Key in Student_Information table:

Rtu_Roll_No

Composite Key:

A composite key is a combination of more than one attribute that can be used to uniquely identify each record. It is also known as the “Compound” key. A composite key may be a candidate or primary key.

Example:

Composite Key in Branch_Info table.

{ Branch_Name, Branch_Code}Composite Key in Student_Information table:
{ Student_Id, Student_Name }

Super Key
A super key is a set of one or more than one keys that can be used to uniquely identify the record in the table. A Super key for an entity is a set of one or more attributes whose combined value uniquely identifies the entity in the entity set. A super key is a combined form of the Primary Key, Alternate Key, and Unique key, and Primary Key, Unique Key, and Alternate Key are subsets of the super key. A Super Key is simply a non-minimal Candidate Key, that is to say, one with additional columns not strictly required to ensure the uniqueness of the row. A super key can have a single column. Foreign Keys in Mysql.

Example:
Super Keys in Branch_Info Table.

Branch_Id
Branch_Name
Branch_Code
{ Branch_Id, Branch_Code }
{ Branch_Name , Branch_Code }

Super Keys in Student_Information Table:

Student_Id
College_Id
Rtu_Roll_No
{ Student_Id, Student_Name}
{ College_Id, Branch_Id }
{ Rtu_Roll_No, Session }

Minimal Super Key:
A minimal super key is a minimum set of columns that can be used to uniquely identify a row. In other words, the minimum number of columns can be combined to give a unique value for every row in the table. Primary Keys vs Foreign Keys.

Example:
Minimal Super Keys in Branch_Info Table.

Branch_Id
Branch_Name
Branch_Code

Minimal Super Keys in Student_Information Table:

Student_Id
College_Id
Rtu_Roll_No

Natural Keys:

A natural key is a key composed of columns that actually have a logical relationship to other columns within a table. For example, if we use Student_Id, Student_Name, and Father_Name columns to form a key then it would be a “Natural Key” because there is definitely a relationship between these columns and other columns that exist in the table. Natural keys are often called “Business Keys” or “Domain Keys”. Foreign Keys in Mysql.

Surrogate Key:
A surrogate key is an artificial key that is used to uniquely identify the record in the table. For example, SQL Server or Sybase database system contains an artificial key that is known as “Identity”. Surrogate keys are just simple sequential numbers. Surrogate keys are only used to act as primary keys. Primary Keys vs Foreign Keys.

Example:
Branch_Id is a Surrogate Key in the Branch_Info table and Student_Id is a Surrogate key in the Student_Information table.

Foreign Keys:
A foreign key is used to generate the relationship between the tables. Foreign Key is a field in a database table that is the Primary key in another table. A foreign key can accept null and duplicate values.

Example:
Branch_Id is a Foreign Key in the Student_Information table the primary key exists in Branch_Info(Branch_Id) table.

Conclusion

The database generally only contains Primary Key, Foreign Key, Unique Key, and Surrogate key and other remaining keys are just concepts. A table must have a unique key. According to Dr. E. F. Codd‘s third rule, “Every single data element (value) is guaranteed to be accessible logically with a combination of table-name, primary-key (row value), and attribute-name (column value)”. So each table must-have keys, because the use of keys makes data highly reliable and provides several types of content like unique data and null values. Foreign Keys in Mysql.

The article was originally published here.

Defining primary key–foreign key relationships

This section describes how to define relationships between a key in one table and a foreign key in another table, using the Data Diagram Editor.

About this task

Columns that define primary keys in one table in a relational model can have a relationship with columns in one or more other tables. The easiest (and recommended) method of defining these relationships using the Data Diagram Editor is provided in the following instructions.

Every table can have (but does not have to have) a primary key. The column or columns defined as the primary key ensure uniqueness in the table; no two rows can have the same key.

The primary key of one table may also help to identify records in other tables and be part of the second table’s primary key. The instance of the first table’s primary key in the second table is referred to as a foreign key.

The relationship between the primary key in one table and the foreign key in another table can be of four types:

Identifying — means that the column dragged to a second table will be added to that table as part of its primary key.
Non-Identifying Optional — means that the column dragged to a second table will not be added to its primary key and that the relationship is optional. (The data foreign key column need not exist in the primary key column in the first table.)
Non-Identifying Mandatory — means that the column dragged to a second table will not be added to its primary key and that the relationship is mandatory. (The data foreign key column must exist in the primary key column in the first table.)
Non-Identifying One-To-One — means that the column dragged to a second table will not be added to its primary key and that the relationship is mandatory and unique. (The data foreign key column must exist in the primary key column in the first table and in a one-to-one relationship.)

In the Decision Optimization Center Data Diagram Editor, you create these primary key-foreign key relationships simply by dragging handles from one table to another table. The arrows on the handles indicate the direction of the relationship; that is, which table is primary and which is foreign. Foreign Keys in Mysql.

The example used to illustrate this in the following procedures is taken from the Getting Started example. We are trying to establish a relationship between the input table PRODUCT and the output table PLAN, adding the PRODUCT.NAME column to the PLAN table as a foreign key.

Procedure

In the Data Diagram Editor, move the mouse pointer over or hover over the table that contains the primary key you want to create a foreign key for. (In this example, the PRODUCT table.)
When you do this, handles appear on the side of the table.
Grab the handle with the outward-pointing arrow and drag it from the first table (the one containing your primary key) onto the second table (the one in which you want to create a foreign key) and release the mouse button.
When you do this, a dialog box appears in which you can select the type of relationship you are trying to define:

In this example, we choose the Create Non-Identifying Mandatory FK Relationship option.
When you choose this option, the columns that defined the first table’s primary key are added to the second table and marked with (FK) to indicate that it is a foreign key. The line drawn between the two tables remains in the diagram to visually indicate the relationship.
The added columns can be modified and be made part of the primary key later if needed, via the standard Properties view.

Note:

If a column already exists in the second table with the same name as the primary key you have dragged there, a dialog box appears to ask how you want to proceed:

Select the proper action and click OK.

Tip:

In addition to using the handles displayed when you hover over a table in the drawing area of the Data Diagram Editor, you can also select the icon for the type of relationship you want to define from the Palette.

Then just drag the cursor from the first table to the second table, and that type of primary key-foreign key relationship is defined. Primary Keys vs Foreign Keys.