Home > DeveloperSection > Articles > Important SQL Keys

Important SQL Keys


Database Database 
Ratings:
0 Comment(s)
 4338  View(s)
Rate this:

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:

           Foreign Key is a field in database table that is Primary key in another table. It can accept multiple null, duplicate values. For more help refer the article Difference between primary key and foreign key.

Example : We can have a DeptID column in the Employee table which is pointing to DeptID column in a department table where it a primary key.

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:

Uniquekey is a set of one or more fields/columns of a table that uniquely identify a record in database table. It is like Primary key but it can accept only one null value and it can not have duplicate values.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 is a set of one or more fields/columns that can identify a record uniquely in a table. There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.

Example: In below diagram ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.

Composite Key:

Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.

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

)


Don't want to miss updates? Please click the below button!

Follow MindStick