Interested to Buy Any Domain ? << Click Here >> for more details...
what is primary key,unique key, foreign key? can u teach me
in simple language?
- 44 Answers
- 186522 Views
- Banking, CMC, CTS, HCL, Infosys, Keggfarms, SkyEss, Sunrise, TCS, Web Synergies, Wipro, I also Faced
- E-Mail Answers
Answer Posted / prachi prakash hasabnis
A primary key (PK) is a column (s) that uniquely identifies
each row in the primary or 'parent' table. The PK not only
ensures there are no duplicate rows but it also provides a
mechanism that allows you to reference all of the
attributes (columns) of a specific row simply by referring
to one numeric value
Example: You can reference any information about a
particular customer simply by referring to that customer's
ID. The PK should be a small numeric key so that it is easy
to sort, store and search
A foreign key (FK) in related or 'child' table inherits the
primary key of the 'parent' or primary table. Thus, a PK-FK
combination establishes a relationship between the parent
and child tables
One-to-Many (1-N)
A one-to-many relationship is the most common type of
relationship
One instance of an entity (the parent) is associated with
zero or several instances of another entity (the child)
The classic example of a one-to-many relationship is that
of 'customers' and 'orders;' each customer (parent) can
have many orders, but each order (child) can be related to
only one customer
To implement this relationship, a 'customer ID' column is
added to the 'orders' table (FK) and referenced
to 'customer ID' column in the 'customers' table (PK)
Many-to-Many (N-N)
In a many-to-many relationship, many instances of one
entity are associated with many instances of another entity
The classic example of a many-to-many relationship is that
of 'orders' and 'parts'. Each order can consist of many
parts and each part can be used in many orders. This type
of relationship cannot be established directly. A third
table, called a junction table or associating table, is
used to establish the relationship. A junction table
typically has a composite PK that consists of two or more
columns that also serve as FKs. In effect you have two one-
to-many relationships linked via the junction table that
together produce a single many-to-many relationship
In the classic 'Order Entry' database example, the junction
table is referred to as the 'order detail' table; it
implements the many-to-many relationship between 'orders'
and 'parts'
The 'order detail' table contains an 'order_ ID' column
that functions as a FK and relates it to the 'orders'
table; the result is a one-to-many relationship
between 'orders' and 'order details'. The 'order detail'
table also contains a 'part_ ID' column that functions as a
FK and relates it to the 'parts' table; the result is a one-
to-many relationship between 'parts' and 'order details'.
Within the 'order detail' table, the 'order_ ID' and
the 'part_ ID' together are defined as a composite primary
key. Taken all together, the two one-to-many relationships
(orders->order_detail and parts->order_ detail) constitute
a many-to- many relationship between 'orders' and 'parts'
One-to-One (1-1)
In a one-to-one relationship, one instance of one entity is
associated with a single instance of another entity
This type of relationship is not very common. In order to
evaluate the need for a one-to-one relationship, ask
yourself why you can't just use one large table. By
implementing a one-to-one relationship you are in effect
vertically partitioning a table (splitting it into two
pieces). There are typically two reasons for vertically
partitioning a table by implementing a one-to-one
relationship: 1) security and 2) performance
Example: You split the employee table into two pieces so
that salary and other sensitive information can be stored
in separate table (security)
Example: Some columns in a large table are not accessed
frequently or contain large data types so they are moved to
a separate table (performance)
In the illustration above, the 'publishers' table is split
into two pieces; one to hold the smaller, more commonly
accessed fields and another to hold the less frequently
requested data fields that include large data types
(the 'logo' field is an image data type and the 'pr_info'
field is a text data type)
Recursive Relationship
A recursive relationship refers to a relationship
established within a table. In other words, a table
is 'related' to itself. Recursive relationships are usually
established in situations where it is useful to perform a
self-join
The classic example of a self-join is the 'employee-
manager' report. In this situation, the 'employees' table
contains name, ID and 'reports to' columns. The 'reports
to' column contains the ID (not the name) of the employee's
manager. In order to prepare a report that lists employees
and their managers by their names, a self-join is required.
In this situation, the 'reports to' column is typically
made a FK that references the PK 'employee ID' field
| Is This Answer Correct ? | 9 Yes | 5 No |
Post New Answer View All Answers
Given a table Employee which has columns name and salary, write an SQL query to find the employee with the second highest salary.
How many indexes can be created on a table?
What is a driver in database?
What are the types of joins?
Please post the scenerois for writing the complex sql queries , for my practice.Can be any query if you cannot solve.
What is redis good for?
Is redis in memory database?
What is a sequence? Explain it with one example?
How to implement database security?
How many types of locks are present in a database?
What is the difference between oracle and MS Access?
What is a database call?
What are the advantages of normalizing a database.
What is ase database?
if i want to display the name of students such that to which city they belong must have count greaer than two
