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])
)
