Important 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 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
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),
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,
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 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,
CONSTRAINT CK_TEST_COMPOSITE PRIMARY KEY (ID,[UID])