SQL Keys

Important SQL Keys

SQL Keys:

In SQL, keys are used to maintain referential integrity among relations. Put simply, this means keys allow tables to reference each other, and each reference will be “correct” every time. Referential integrity also prevents records from being “dangled” or “orphaned” by another record that has been deleted. There are lots of SQL keys which are widely used in SQL environment in which are all keys some are defined as follows:

Primary Key:

 Primary key are those key which are uniquely defined in a database table or we can say that a database table can contain only one primary key. A primary key is used to uniquely identify each row in a table. It can either be part of the actual record itself, or it can be an artificial field (one that has nothing to do with the actual record). A primary key can consist of one or more fields on a table. When multiple fields are used as a primary key, they are called a composite key. Primary Key enforces uniqueness of the column on which they are defined. Primary Key creates a clustered index on the column. Primary Key does not allow Nulls.

Example: Creating Primary Key in database table

create table PrimarkeyTest  // primarykeytest is table name

(

id int not null primary key,// create primary key constraints on id column

name varchar(20)

)

Foreign Key:

                A foreign key is a field (or fields) that points to the primary key of another table. The purpose of the foreign key is to ensure referential integrity of the data. In other words, only values that are supposed to appear in the database are permitted.

Example: Creating foreign key in database table

create table foreignkeytest // foreignkeytest is a table name

(

--// create foreign key  which references to primarykeytest table id column

fid int references  PrimarkeyTest(id),

name varchar(20)

)

Unique Key:

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

Unique keys do not enforce the NOT NULL constraint in practice. Because NULL is not an actual when two rows are compared, and both rows have NULL in a column, the column values are not considered to be equal. Thus, in order for a unique key to uniquely identify each row in a table, NULL values must not be used. However, a column defined as a unique key column allows only one NULL value, which in turn can uniquely identify that row.

Example: Creating Unique Key in database table

create table Uniquekeytest

(

--// create unique key  in Uniquekeytest table uid column

uid int unique,

name varchar(20)

)

Candidate Key:

A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.

Composite Key:

Composite key is nothing more than a primary key which consist of more than one column to uniquely identify row of table. That is composite key is a combination of more than one column to uniquely identify the row of table.

Example: Creating Composite key With Create command

----DEMONSTRATION OF CREATING COMPOSITE KEY WITH CTREATE TABLE COMMAND-----

CREATE TABLE TEST_COMPOSITE

(

 ID INT NOT NULL,

 [UID] INT NOT NULL,

 NAME VARCHAR(20)

 CONSTRAINT CK_TEST_COMPOSITE PRIMARY KEY (ID,[UID])

)