1-Primary key is a set of one or more fields/columns of a table that uniquely identify a record in database table.
2-It can't accept null, duplicate values. Only one Candidate Key can be Primary Key.
3-By default, Primary key is clustered index and data in the database table is physically organized in the sequence of clustered index.
4-We can have only one Primary key in a table.
5-DEFINE- DeptID PRIMARY KEY
6-We can generated ID automatically with the help of Auto Increment field. Primary key supports Auto Increment value.
1-Foreign key is a field in the table that is primary key in another table.
2-Foreign key can accept multiple null value.
3-Foreign key do not automatically create an index, clustered or non-clustered. You can manually create an index on foreign key.
4-We can have more than one foreign key in a table.
5-DEFINE- DeptID int FOREIGN KEY REFERENCES Department(DeptID)
6-Foreign key not supports Auto Increment value.
1-Unique key uniquely identify a record in the table.
2-Unique key can accept only one null value.
3-By default, Unique key is a unique non-clustered index.
4-We can have more than one unique key in a table.
5-DEFINE- DeptID UNIQUE KEY
6-Unique key not supports Auto Increment value.
1-super key is the combination of fields by which the row is uniquely identified.
2-A Super Key identifies uniquely rows/tuples in a table/relation of a database.
3-If you add any other Column/Attribute to a Primary Key then it become a Super Key, like EmployeeID + EmployeeName is a Super Key.
4-Primary key, Unique key, Alternate key are subset of Super Keys.
1) A Alternate key is a key that can be work as a primary key. Basically it is a candidate key that currently is not primary key.
Example: In below diagram RollNo and EnrollNo becomes Alternate Keys when we define ID as Primary Key.
1) Composite Key is a combination of more than one fields/columns of a table. It can be a Candidate key, Primary key.
Minimal superkey (Candidate key):
1-Minimal super key is call candidate key, A minimal superkey is the minimum number of columns that can be used to uniquely identify a single row.
2-Yes, a table can have multiple minimal superkeys.
3-A candidate key is the most minimal subset of fields that uniquely identifies a tuple(rows).
4-There can be multiple Candidate Keys in one table. Each Candidate Key can work as Primary Key.
5-Example: In below diagram- ID, RollNo and EnrollNo are Candidate Keys since all these three fields can be work as Primary Key.
Primary & candidate key:
1) Both Primary and Candidate keys can uniquely identify records in a table on database.
2) Both Primary and Candidate keys are has constraints UNIQUE and NOT NULL.
3) Primary key or Candidate keys can be either single column or combination of multiple columns in a table.