none
Candidate Key and Primary Key?

    Question

  • Hi,

    1. What is Candidate Key and Primary Key?
    2. What is Composite Key and Super Key?


    Could Any body Explain in detail.............?
    Handa
    Monday, October 26, 2009 2:54 AM

Answers

  • You can actually find the answers here in MSDN just by searching it.

    A CANDIDATE key is an index expression that does not allow duplicate or NULL values.
    By definition, a candidate key is a single field or a composite expression that meets the requirements of a primary key. It is a database design requirement that tables have one and only one primary key. A table might however contain several unique identifiers. Once the primary key has been identified, other unique keys are referred to as candidate keys.

    In Visual FoxPro, primary keys are used to reinforce data integrity, and are therefore only available for tables included in a database. Free tables on the other hand can have candidate keys. Candidate keys can be used to ensure that a record is unique.

    Following are two scenarios that might require the use of a candidate key in addition to a primary key.

    Employee table: you have a table that records information about employees. Each employee can be identified by an employee identification number (primary key) and also by a social security number (candidate key).

    Medical system: a table records patient information. Two fields can uniquely identify each patient: a medical record number (unique key) and a social security number (candidate key). The primary key provides a more intuitive way to search the data.

    A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.

    A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.

    When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes .

    If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

     

    Composite Key

    • This is a very basic SQL topic but I think it is necessary to clarify the concept.
    • A Primary Key uniquely identifies each row in a table, it is not always a single-column key,it could be
      • a single-column key
      • or a composite key
    • A primary key can consist of one or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
    • For example, if you have a Student table and a Course table, and one student can select many courses and one course can be selected by many students, so this is a many-to-many relationship. So you need to create the third table to define the relationship, say it's called StudentCourse. It is important to note that you only need the StudentID and CourseID in this table as a composite key. You do not need an extra identity ID column in this table to uniquely identifies each row because only having an ID column to uniquely identifies each row is not sufficient. It cannot prevent the same student selecting the same course from being inserted into this table.

    A superkey is defined in the relational model of database organization as a set of attributes of a relation variable (relvar ) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a superkey can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent .

    Note that if attribute set K is a superkey of relvar R , then at all times it is the case that the projection of R over K has the same cardinality as R itself.

    Informally, a superkey is a set of columns within a table whose values can be used to uniquely identify a row. A candidate key is a minimal set of columns necessary to identify a row, this is also called a minimal superkey. For example, given an employee table, consisting of the columns employeeID, name, job, and departmentID, we could use the employeeID in combination with any or all other columns of this table to uniquely identify a row in the table. Examples of superkeys in this table would be {employeeID, Name}, {employeeID, Name, job}, and {employeeID, Name, job, departmentID}.

    In a real database we don't need values for all of those columns to identify a row. We only need, per our example, the set {employeeID}. This is a minimal superkey – that is, a minimal set of columns that can be used to identify a single row. So, employeeID is a candidate key .

    ---------------------------------------------------------------------------------------------------------------------------------------
    Haha.. got all this on the internet... I think it's a good idea to search first...
    • Marked as answer by Ritesh Handa Thursday, October 29, 2009 8:13 AM
    Monday, October 26, 2009 3:12 AM
  • In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that

    1 the relation does not have two distinct tuples with the same values for these attributes

    2 there is no proper subset of these attributes for which (1) holds.

     

    please see this link

    http://en.wikipedia.org/wiki/Candidate_key


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Ritesh Handa Thursday, October 29, 2009 8:13 AM
    Monday, October 26, 2009 3:07 AM



  • what is basic need to make a candidate Key?

    Is candidate key  only name convention..................?


    Handa
    • Marked as answer by Ritesh Handa Thursday, October 29, 2009 8:13 AM
    Monday, October 26, 2009 3:25 AM



  • what is basic need to make a candidate Key?

    Is candidate key  only name convention..................?


    Handa

    Candidate Key column must be UNIQUE and no nulls.

    Candidate Key is conceptual term when designing tables.  For example, SSIS Data Profiler identifies potential Primary Key as Candidate Keys.

    Primary Key, Unique Key and Foreign Key are real constraints - database objects.

    PK & UK require UNIQUE index. FK is not unique unless it doubles as PK also.

    Surrogate Key, Candidate Key and Natural Key are logical data modelling terms.

    If you remove the PK constraint in AdventureWorks.Production.Product table, it has 4 Candidate Keys:

    ProductID
    Name
    ProductNumber
    rowguid

    I know the above because ProductID is PK and the other 3 has unique index on them and no nulls.

    The designer picked ProductID (int identity(1,1)) to become the Primary Key.  In this capacity as a meaningless number, the ProductID Primary Key is called a Surrogate Key (surrogate to the Natural Keys: Name, ProductNumber). Rowguid also meaningless number, used for row versioning (replication).  Name & ProductNumber (more or less) are meaningful keys.

    SELECT TOP (5) ProductID, Name, ProductNumber, rowguid
    FROM AdventureWorks2008.Production.Product
    ORDER BY NEWID()
    /*
    ProductID	 Name				ProductNumber	rowguid
    742	HL Mountain Frame - Silver, 46	FR-M94S-46	A189D86E-D923-4336-B13D-A5DB6F426540
    789	Road-250 Red, 44			BK-R89R-44	0AA71AD6-AFAF-43C6-9745-35D815B50A5B
    492	Paint - Black			PA-187B		DF20E514-3D47-491B-9454-0911EC3F7D29
    413	Internal Lock Washer 4		LI-1200		7F7413BB-BAD2-47E4-9BC4-D98B194BE35D
    709	Mountain Bike Socks, M		SO-B909-M		18F95F47-1540-4E02-8F1F-CC1BCB6828D0
    */


    In the following example the Candidate Keys are defined as UNIQUE keys, that means, they can be referenced by Foreign Keys. Normally though, you only place a UNIQUE INDEX on Candidate Keys (or Natural Keys) since you use the Primary Key for referencing.

    CLUSTERED UNIQUE INDEX is created for the PK and non-clustered unique indexes for the UK-s. All PK & UK-s are constraints as database objects. Clustered is not a requirement for PK only UNIQUE INDEX is, SQL Server implements it that way as default unless you define it differently.

    USE tempdb; 
    
    -- Table with PRODUCTID PRIMARY KEY and 3 UNIQUE KEYs 
    CREATE TABLE Product ( 
      ProductId    INT    IDENTITY ( 1 , 1 )    PRIMARY KEY, 
      ProductName  VARCHAR(32)    UNIQUE, 
      ProductNo    VARCHAR(12)    UNIQUE, 
      Decription   VARCHAR(256)    NOT NULL, 
      rowguid      UNIQUEIDENTIFIER    DEFAULT (newid())    UNIQUE, 
      ModifiedDate SMALLDATETIME    DEFAULT (getdate())); 
    
    -- ProductID is FOREIGN KEY 
    CREATE TABLE ProductPhoto ( 
      ProductPhotoID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY, 
      ProductID      INT     references Product(ProductID), 
      Size           CHAR(2), 
      Photo          VARBINARY(MAX), 
      ModifiedDate   SMALLDATETIME    DEFAULT (getdate())); 





    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, October 26, 2009 8:59 AM

All replies

  • In the relational model of databases, a candidate key of a relation is a minimal superkey for that relation; that is, a set of attributes such that

    1 the relation does not have two distinct tuples with the same values for these attributes

    2 there is no proper subset of these attributes for which (1) holds.

     

    please see this link

    http://en.wikipedia.org/wiki/Candidate_key


    This posting is provided "AS IS" with no warranties, and confers no rights.
    • Marked as answer by Ritesh Handa Thursday, October 29, 2009 8:13 AM
    Monday, October 26, 2009 3:07 AM
  • You can actually find the answers here in MSDN just by searching it.

    A CANDIDATE key is an index expression that does not allow duplicate or NULL values.
    By definition, a candidate key is a single field or a composite expression that meets the requirements of a primary key. It is a database design requirement that tables have one and only one primary key. A table might however contain several unique identifiers. Once the primary key has been identified, other unique keys are referred to as candidate keys.

    In Visual FoxPro, primary keys are used to reinforce data integrity, and are therefore only available for tables included in a database. Free tables on the other hand can have candidate keys. Candidate keys can be used to ensure that a record is unique.

    Following are two scenarios that might require the use of a candidate key in addition to a primary key.

    Employee table: you have a table that records information about employees. Each employee can be identified by an employee identification number (primary key) and also by a social security number (candidate key).

    Medical system: a table records patient information. Two fields can uniquely identify each patient: a medical record number (unique key) and a social security number (candidate key). The primary key provides a more intuitive way to search the data.

    A table typically has a column or combination of columns that contain values that uniquely identify each row in the table. This column, or columns, is called the primary key (PK) of the table and enforces the entity integrity of the table. You can create a primary key by defining a PRIMARY KEY constraint when you create or modify a table.

    A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values. Because PRIMARY KEY constraints guarantee unique data, they are frequently defined on an identity column.

    When you specify a PRIMARY KEY constraint for a table, the Database Engine enforces data uniqueness by creating a unique index for the primary key columns. This index also permits fast access to data when the primary key is used in queries. Therefore, the primary keys that are chosen must follow the rules for creating unique indexes .

    If a PRIMARY KEY constraint is defined on more than one column, values may be duplicated within one column, but each combination of values from all the columns in the PRIMARY KEY constraint definition must be unique.

     

    Composite Key

    • This is a very basic SQL topic but I think it is necessary to clarify the concept.
    • A Primary Key uniquely identifies each row in a table, it is not always a single-column key,it could be
      • a single-column key
      • or a composite key
    • A primary key can consist of one or more columns of a table. When two or more columns are used as a primary key, they are called a composite key. Each single column's data can be duplicated but the combination values of these columns cannot be duplicated.
    • For example, if you have a Student table and a Course table, and one student can select many courses and one course can be selected by many students, so this is a many-to-many relationship. So you need to create the third table to define the relationship, say it's called StudentCourse. It is important to note that you only need the StudentID and CourseID in this table as a composite key. You do not need an extra identity ID column in this table to uniquely identifies each row because only having an ID column to uniquely identifies each row is not sufficient. It cannot prevent the same student selecting the same course from being inserted into this table.

    A superkey is defined in the relational model of database organization as a set of attributes of a relation variable (relvar ) for which it holds that in all relations assigned to that variable there are no two distinct tuples (rows) that have the same values for the attributes in this set. Equivalently a superkey can also be defined as a set of attributes of a relvar upon which all attributes of the relvar are functionally dependent .

    Note that if attribute set K is a superkey of relvar R , then at all times it is the case that the projection of R over K has the same cardinality as R itself.

    Informally, a superkey is a set of columns within a table whose values can be used to uniquely identify a row. A candidate key is a minimal set of columns necessary to identify a row, this is also called a minimal superkey. For example, given an employee table, consisting of the columns employeeID, name, job, and departmentID, we could use the employeeID in combination with any or all other columns of this table to uniquely identify a row in the table. Examples of superkeys in this table would be {employeeID, Name}, {employeeID, Name, job}, and {employeeID, Name, job, departmentID}.

    In a real database we don't need values for all of those columns to identify a row. We only need, per our example, the set {employeeID}. This is a minimal superkey – that is, a minimal set of columns that can be used to identify a single row. So, employeeID is a candidate key .

    ---------------------------------------------------------------------------------------------------------------------------------------
    Haha.. got all this on the internet... I think it's a good idea to search first...
    • Marked as answer by Ritesh Handa Thursday, October 29, 2009 8:13 AM
    Monday, October 26, 2009 3:12 AM



  • what is basic need to make a candidate Key?

    Is candidate key  only name convention..................?


    Handa
    • Marked as answer by Ritesh Handa Thursday, October 29, 2009 8:13 AM
    Monday, October 26, 2009 3:25 AM



  • what is basic need to make a candidate Key?

    Is candidate key  only name convention..................?


    Handa

    Candidate Key column must be UNIQUE and no nulls.

    Candidate Key is conceptual term when designing tables.  For example, SSIS Data Profiler identifies potential Primary Key as Candidate Keys.

    Primary Key, Unique Key and Foreign Key are real constraints - database objects.

    PK & UK require UNIQUE index. FK is not unique unless it doubles as PK also.

    Surrogate Key, Candidate Key and Natural Key are logical data modelling terms.

    If you remove the PK constraint in AdventureWorks.Production.Product table, it has 4 Candidate Keys:

    ProductID
    Name
    ProductNumber
    rowguid

    I know the above because ProductID is PK and the other 3 has unique index on them and no nulls.

    The designer picked ProductID (int identity(1,1)) to become the Primary Key.  In this capacity as a meaningless number, the ProductID Primary Key is called a Surrogate Key (surrogate to the Natural Keys: Name, ProductNumber). Rowguid also meaningless number, used for row versioning (replication).  Name & ProductNumber (more or less) are meaningful keys.

    SELECT TOP (5) ProductID, Name, ProductNumber, rowguid
    FROM AdventureWorks2008.Production.Product
    ORDER BY NEWID()
    /*
    ProductID	 Name				ProductNumber	rowguid
    742	HL Mountain Frame - Silver, 46	FR-M94S-46	A189D86E-D923-4336-B13D-A5DB6F426540
    789	Road-250 Red, 44			BK-R89R-44	0AA71AD6-AFAF-43C6-9745-35D815B50A5B
    492	Paint - Black			PA-187B		DF20E514-3D47-491B-9454-0911EC3F7D29
    413	Internal Lock Washer 4		LI-1200		7F7413BB-BAD2-47E4-9BC4-D98B194BE35D
    709	Mountain Bike Socks, M		SO-B909-M		18F95F47-1540-4E02-8F1F-CC1BCB6828D0
    */


    In the following example the Candidate Keys are defined as UNIQUE keys, that means, they can be referenced by Foreign Keys. Normally though, you only place a UNIQUE INDEX on Candidate Keys (or Natural Keys) since you use the Primary Key for referencing.

    CLUSTERED UNIQUE INDEX is created for the PK and non-clustered unique indexes for the UK-s. All PK & UK-s are constraints as database objects. Clustered is not a requirement for PK only UNIQUE INDEX is, SQL Server implements it that way as default unless you define it differently.

    USE tempdb; 
    
    -- Table with PRODUCTID PRIMARY KEY and 3 UNIQUE KEYs 
    CREATE TABLE Product ( 
      ProductId    INT    IDENTITY ( 1 , 1 )    PRIMARY KEY, 
      ProductName  VARCHAR(32)    UNIQUE, 
      ProductNo    VARCHAR(12)    UNIQUE, 
      Decription   VARCHAR(256)    NOT NULL, 
      rowguid      UNIQUEIDENTIFIER    DEFAULT (newid())    UNIQUE, 
      ModifiedDate SMALLDATETIME    DEFAULT (getdate())); 
    
    -- ProductID is FOREIGN KEY 
    CREATE TABLE ProductPhoto ( 
      ProductPhotoID INT    IDENTITY ( 1 , 1 )    PRIMARY KEY, 
      ProductID      INT     references Product(ProductID), 
      Size           CHAR(2), 
      Photo          VARBINARY(MAX), 
      ModifiedDate   SMALLDATETIME    DEFAULT (getdate())); 





    Kalman Toth SQL SERVER 2012 & BI TRAINING
    New Book: Beginner Database Design & SQL Programming Using Microsoft SQL Server 2012


    Monday, October 26, 2009 8:59 AM