what is primary key,unique key, foreign key? can u teach me
in simple language?
- 44 Answers
- 174659 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
Give the syntax of exception handling used in DBMS?
Explain normalization in one line?
What are different types of blocks used in stored procedure?
What is a reference table in database?
After normalization, what are the conditions which have to keep in mind to de-normalize it?
can i use the commands "modify" and Change in DB2 to alter my column datatype and its name in a table?
First input columns brand, mt, re values are ov, 1,re vg, 2,re wu ,3,re. Second input columns are brand, mt, cx their records are ov,4,vg ,5,cx Wu, 6,cx and third input columns brand, mt, rt values are ov,7,rt vg, 8,rt wu, 9,rt but my output is brand, re, cx,rt values are ov, 1,4,7 vg, 2,5,8 wu, 3,6,9
How do I manually create a database?
Explain transaction manager?
What is difference between clustered, non-clustered and unique index?
What are the validations performed on the source data? If there are junk data available in the source data, how to eliminate it?
What are the advantages of views in a database?
Explain entity ?
What are different types of clusters present?
Hi, I want do a course in DWH Informatica Administrator and Abinito Administrator please tell me some institute in chennai and i am searching for a job,so please help me and my mail id : satty_rh@yahoo.co.in Thanks, Kumar
