Information about Primary Key

In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns. No two distinct rows in a table can have the same value (or combination of values) in those columns. Depending on its design, a table may have arbitrarily many unique keys but at most one primary key.

A unique key must uniquely identify all possible rows that exist in a table and not only the currently existing rows. Examples of unique keys are Social Security numbers (associated with a specific person) or ISBNs (associated with a specific book). Telephone books and dictionaries cannot use names or words or Dewey Decimal system numbers as candidate keys because they do not uniquely identify telephone numbers or words.

A primary key is a special case of unique keys. The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is. Thus, the values in a unique key columns may or may not be NULL. Another difference is that primary keys must be defined using another syntax.

The relational model, as expressed through relational calculus and relational algebra, does not distinguish between primary keys and other kinds of keys. Primary keys were added to the SQL standard mainly as a convenience to the application programmer.

Unique keys as well as primary keys can be referenced by foreign keys.

Defining Primary Keys

Primary keys are defined in the ANSI SQL Standard, through the PRIMARY KEY constraint. The syntax to add such a constraint to an existing table is defined in SQL:2003 like this:

>
  ALTER TABLE  
      ADD [ CONSTRAINT  ] 
      PRIMARY KEY (  {, }... )




The primary key can also be specified directly during table creation. In the SQL Standard, primary keys may consist of one or multiple columns. Each column participating in the primary key is implicitly defined as NOT NULL. Note that some DBMS require that primary key columns are explicitly marked as being NOT NULL.



>
  CREATE TABLE table_name (
     id_col  INT,
     col2    CHARACTER VARYING(20),
     ...
     CONSTRAINT tab_pk PRIMARY KEY(id_col),
     ...
  )




If the primary key consists only of a single column, the column can be marked as such using the following syntax:



>
  CREATE TABLE table_name (
     id_col  INT  PRIMARY KEY,
     col2    CHARACTER VARYING(20),
     ...
  )

Defining Unique Keys
The definition of unique keys is syntactically very similar to primary keys.



>
  ALTER TABLE  
      ADD [ CONSTRAINT  ] 
      UNIQUE (  {, }... )




Likewise, unique 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,
     ...
     CONSTRAINT key_uniqe UNIQUE(key_col),
     ...
  )




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

Surrogate Keys
In some design situations the natural key that uniquely identifies a tuple in a relation is difficult to use for software development. For example, it may involve multiple columns or large text fields. A surrogate key can be used as the primary key. In other situations there may be more than one candidate key for a relation, and no candidate key is obviously preferred. A surrogate key may be used as the primary key to avoid giving one candidate key artificial primacy over the others.



Since primary keys exist primarily as a convenience to the programmer, surrogate primary keys are often used - in many cases exclusively - in database application design.



Due to the popularity of surrogate primary keys, many developers and in some cases even theoreticians have come to regard surrogate primary keys as an inalienable part of the relational data model. This is largely due to a migration of principles from the Object-Oriented Programming model to the relational model, creating the hybrid object-relational model. In the ORM, these additional restrictions are placed on primary keys:
Primary keys should be immutable, that is, not change until the record is destroyed.
Primary keys should be anonymous integer or numeric identifiers.

However, neither of these restrictions are part of the relational model or any SQL standard. Due diligence should be applied when deciding on the immutability of primary key values during database and application design. Some database systems even imply that values in primary key columns cannot be changed using the UPDATE SQL statement.




Topics in database management systems (DBMS)
 •  • [ edit]


Concepts

Database 
Database models 
Database storage 
Relational model 
Distributed DBMS 
ACID 
Null

Relational database 
Relational algebra 
Relational calculus 
Database normalization 
Referential integrity 
Relational DBMS 

Primary key, Foreign key, Surrogate key, Superkey, Candidate key


Objects

Trigger 
View 
Table 
Cursor 
Log 
Transaction 
Index 

Stored procedure 
Partition

Topics in SQL

Select 
Insert 
Update 
Merge 
Delete 
Join 
Union 
Create 
Drop

Begin work 
Commit 
Rollback 
Truncate 
Alter

Implementations of database management systems


Types of implementations

Relational 
Flat file 
Deductive 
Dimensional 
Hierarchical 
Object oriented 
Object relational 
Temporal 
XML data stores


Database products

Object-oriented (comparison) 
Relational (comparison)

Components

Query language 
Query optimizer 
Query plan 
ODBC 
JDBC
 A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged).
..... Click the link for more information. 
 In the relational model, a candidate key of a relvar (relation variable) is a set of attributes of that relvar such that 
(1) at all times it holds in the relation assigned to that variable that there are no two distinct tuples with the same values for these attributes and

..... Click the link for more information. 
 In the context of a relational database, a row—also called a record or tuple—represents a single, implicitly structured data item in a table. In simple terms, a database table can be thought of as consisting of rows and columns or fields.
..... Click the link for more information. 
 table is a set of data elements (values) that is organized using a model of horizontal rows and vertical columns. The columns are identified by name, and the rows are identified by the values appearing in a particular column subset which has been identified as a candidate key.
..... Click the link for more information. 
 In the context of a relational database table, a column is a set of data values of a particular simple type, one for each row of the table.[1] The columns provide the structure according to which the rows are composed.
..... Click the link for more information. 
 
 ''For the similar-sounding numeral system see duodecimal system.


The Dewey Decimal Classification (DDC, also called the Dewey Decimal System
..... Click the link for more information. 
 Null is a special marker used to indicate that a data value is unknown in the Structured Query Language (SQL). Introduced by the creator of the relational database model, Dr. E.F.
..... Click the link for more information. 
 In the context of relational databases, a foreign key is a referential constraint between two tables.[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.
..... Click the link for more information. 
 A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.
..... Click the link for more information. 
 A database management system (DBMS) is computer software designed for the purpose of managing databases. Typical examples of DBMSs include Oracle, DB2, Microsoft Access, Microsoft SQL Server, PostgreSQL, MySQL, FileMaker and Sybase Adaptive Server Enterprise.
..... Click the link for more information. 
 database is a structured collection of records or data that is stored in a computer system so that a computer program or person using a query language can consult it to answer queries. The records retrieved in answer to queries are information that can be used to make decisions.
..... Click the link for more information. 
 A data model is not just a way of structuring data: it also defines a set of operations that can be performed on the data. The relational model, for example, defines operations such as select, project, and join.
..... Click the link for more information. 
 flat file database describes any of various means to encode a data model (most commonly a table) as a plain text file. 
Unordered
 Unordered storage typically stores the records in the order they are inserted, while having 
..... Click the link for more information. 
 The relational model for database management is a database model based on predicate logic and set theory. It was first formulated and proposed in 1969 by Edgar Codd with aims that included avoiding, without loss of completeness, the need to write computer programs to
..... Click the link for more information. 
 A distributed database management system is a software system that permits the management of a distributed database and makes the distribution transparent to the users. A distributed database is a collection of multiple, logically interrelated databases distributed over a computer network.
..... Click the link for more information. 
 ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. In the context of databases, a single logical operation on the data is called a transaction.
..... Click the link for more information. 
 Null is a special marker used to indicate that a data value is unknown in the Structured Query Language (SQL). Introduced by the creator of the relational database model, Dr. E.F.
..... Click the link for more information. 
 A relational database is a database that conforms to the relational model, and refers to a database's data and schema (the database's structure of how that data is arranged).
..... Click the link for more information. 
 Relational algebra, an offshoot of first-order logic, is a set of relations closed under operators. Operators operate on one or more relations to yield a relation. Relational algebra is a part of computer science.
..... Click the link for more information. 
 The relational calculus refers to the two calculi, the tuple relational calculus and the domain relational calculus, that are part of the relational model for databases and that provide a declarative way to specify database queries.
..... Click the link for more information. 
 Database normalization  is a technique for designing relational database tables to minimize duplication of information and, in so doing, to safeguard the database against certain types of logical or structural problems, namely data anomalies.
..... Click the link for more information. 
 referential integrity. In this example, there is a foreign key (artist_id) value in the album table that references a non-existent artist — in other words there is a foreign key value with no corresponding primary key value in the referenced table.
..... Click the link for more information. 
 A relational database management system (RDBMS) is a database management system (DBMS) that is based on the relational model as introduced by E. F. Codd. Relational databases are the most common kind of database in use today (assuming one does not count a file system as a database).
..... Click the link for more information. 
 In relational database design, a unique key or primary key is a candidate key to uniquely identify each row in a table. A unique key or primary key comprises a single column or set of columns.
..... Click the link for more information. 
 In the context of relational databases, a foreign key is a referential constraint between two tables.[1] The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.
..... Click the link for more information. 
 A surrogate key in a database is a unique identifier for either an entity in the modeled world or an object in the database. The surrogate key is not derived from application data.
..... Click the link for more information. 
 superkey is defined in the relational model of database organisation 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
..... Click the link for more information. 
 In the relational model, a candidate key of a relvar (relation variable) is a set of attributes of that relvar such that 
(1) at all times it holds in the relation assigned to that variable that there are no two distinct tuples with the same values for these attributes and

..... Click the link for more information. 
 A database trigger is procedural code that is automatically executed in response to certain events on a particular table in a database. Triggers can restrict access to specific data, perform logging, or audit data modifications.
..... Click the link for more information. 
 In database theory, a view is a virtual or logical table composed of the result set of a query. Unlike ordinary tables (base tables) in a relational database, a view is not part of the physical schema: it is a dynamic, virtual table computed or collated from data in the database.
..... Click the link for more information.