Keys and Integrity Constraints in DBMS: Types, Rules and Worked Examples

Keys can look like a vocabulary chapter until a relation asks you to find every candidate key and predict which insert or delete will fail. The real difficulty is keeping minimality, uniqueness, entity integrity and referential integrity separate while applying them to one schema. A single university database of departments, students, courses and enrolments is small enough to hold in your head and rich enough to test all four, which is why GATE CS preparation keeps returning to a schema of exactly this shape.

Keys and integrity constraints solve two different problems

A key is a set of attributes whose values identify a tuple in every legal state of a relation. An integrity constraint decides which database states and updates are legal. Here, StudentID = 101 identifies Asha, while the foreign-key rule prevents her DeptID = 10 from pointing to a department that does not exist.

The university database is:

ENROLMENT has no single identifying column. Student 101 appears twice, DBMS201 appears twice, and all three rows share the term 2026S1, so its key has to be built from more than one attribute.

Superkeys, candidate keys, primary keys and foreign keys

Assume StudentID and Email are unique in STUDENT. {StudentID} and {Email} are candidate keys. Select {StudentID} as the primary key and {Email} becomes an alternate key. {StudentID, Email} is a superkey, not a candidate key, because either attribute can be removed without losing uniqueness.

In ENROLMENT, the primary key is {StudentID, CourseID, Term}. Student 101 can take DBMS201 in different terms, and many students can take it in the same term. StudentID and CourseID are also foreign keys referencing STUDENT(StudentID) and COURSE(CourseID).

Email is meaningful but may change; StudentID is compact and stable. Neither is universally best. Candidate means minimal by set inclusion, not shortest text or the selected primary key.

Worked example: derive every candidate key with attribute closure

Consider REGISTRATION(StudentID, Email, CourseID, Term, Grade) with these dependencies:

• StudentID -> Email

• Email -> StudentID

• {StudentID, CourseID, Term} -> Grade

CourseID and Term never appear on a right-hand side, so every candidate key must contain both.

Start with {StudentID, CourseID, Term}+:

1. Begin with {StudentID, CourseID, Term}.

2. StudentID -> Email adds Email.

3. {StudentID, CourseID, Term} -> Grade adds Grade.

4. The closure now contains all five attributes.

It is minimal. Removing StudentID leaves no way to obtain StudentID, Email or Grade. Removing CourseID means CourseID cannot be recovered, and removing Term means Term cannot be recovered.

For {Email, CourseID, Term}+, Email -> StudentID adds StudentID, then the composite dependency adds Grade. It reaches all five attributes, and the same removal test proves minimality.

There are exactly two candidate keys: {StudentID, CourseID, Term} and {Email, CourseID, Term}. Each contains CourseID, Term and one member of the reversible identity pair. {StudentID, Email, CourseID, Term} is only a superkey because Email is extraneous. Both are legitimate declarations. Choosing {StudentID, CourseID, Term} as the primary key makes {Email, CourseID, Term} an alternate key, enforced with a UNIQUE constraint rather than a PRIMARY KEY clause. That is the step where a derivation becomes a schema.

Domain, key, entity and referential integrity in one schema

Domain constraints control permitted column values. Here, StudentID is an integer, CourseID is a fixed-width CHAR(7) code, Age must be from 16 through 80, and Grade must be A, A-, B+, B, C, F or temporarily NULL. Thus (103, 'neha@kg.ai', 'Neha', 10, 14) fails the age check even though its keys are unique. Fixed width also explains why DBMS201 fills CourseID exactly while the shorter OS202 sits padded to the same seven characters.

STUDENT.StudentID must be unique and non-null, so a second StudentID = 101 or any StudentID = NULL is illegal. The composite primary key likewise rejects another (101, 'DBMS201', '2026S1', ...) enrolment, even with a different grade.

Referential integrity checks parent rows. DeptID = 10 is valid because department 10 exists; DeptID = 30 is invalid until department 30 is inserted. Foreign keys may repeat, so several students may use DeptID = 10. Nullability is separate, and this schema declares DeptID NOT NULL.

Encode the rules in SQL and predict outcomes

The schema expresses those decisions directly:

CREATE TABLE DEPARTMENT (
  DeptID INT PRIMARY KEY,
  DeptName VARCHAR(40) UNIQUE NOT NULL
);

CREATE TABLE STUDENT (
  StudentID INT PRIMARY KEY,
  Email VARCHAR(120) UNIQUE NOT NULL,
  Name VARCHAR(60) NOT NULL,
  DeptID INT NOT NULL,
  Age INT CHECK (Age BETWEEN 16 AND 80),
  FOREIGN KEY (DeptID) REFERENCES DEPARTMENT(DeptID) ON DELETE RESTRICT
);

CREATE TABLE COURSE (
  CourseID CHAR(7) PRIMARY KEY,
  Title VARCHAR(80) NOT NULL
);

CREATE TABLE ENROLMENT (
  StudentID INT,
  CourseID CHAR(7),
  Term CHAR(6),
  Grade VARCHAR(2),
  PRIMARY KEY (StudentID, CourseID, Term),
  FOREIGN KEY (StudentID) REFERENCES STUDENT(StudentID),
  FOREIGN KEY (CourseID) REFERENCES COURSE(CourseID),
  CHECK (Grade IN ('A', 'A-', 'B+', 'B', 'C', 'F') OR Grade IS NULL)
);

Now predict four updates:

1. Inserting (103, 'neha@kg.ai', 'Neha', 30, 21) into STUDENT fails only because department 30 is absent.

2. Inserting (30, 'ME') into DEPARTMENT first makes that student row legal.

3. Inserting (101, 'DBMS201', '2026S1', 'B') into ENROLMENT fails because the composite primary-key value already exists.

4. Deleting department 10 fails under ON DELETE RESTRICT while student 101 references it.

RESTRICT, CASCADE and SET NULL represent different business rules. SQL products can differ in how UNIQUE interacts with NULL; a primary key, however, is always unique and non-null. You can practise constraint outcomes inside SQL questions after predicting these four by hand.

Common traps and how to repair the reasoning

• Calling every unique-looking column a candidate key. A key follows from declared semantics or dependencies across every legal state. Asha and Ravi having different names in this snapshot does not make Name a key.

• Stopping when a closure reaches every attribute. Test minimality too. {StudentID, Email, CourseID, Term} determines everything, but removing Email leaves a candidate key. This is the same dependency reasoning used in DBMS normalization practice.

• Assuming foreign keys must be unique, non-null or primary keys. STUDENT.DeptID = 10 may repeat. It is non-null only because this schema says so, and it references DEPARTMENT.DeptID without identifying a student.

How GATE-style questions test this topic

An attribute-closure question may ask for all keys, not just one. For REGISTRATION, the answer is exactly two: {StudentID, CourseID, Term} and {Email, CourseID, Term}.

A legal-operation question starts from a stated snapshot. Adding (104, 'mira@kg.ai', 'Mira', 20, 22) is legal. Adding (105, 'ravi@kg.ai', 'Rohan', 20, 22) violates unique Email. In the original snapshot, enrolling student 103 first violates referential integrity. Deleting OS202 while its enrolment exists is rejected under the example's no-action or restrict rule.

For assertions: every candidate key is a superkey, but not every superkey is a candidate key. A relation may have several candidate keys but only one selected primary key. A foreign-key value need not be unique in the child table.

Short version and the next practice step

• A superkey uniquely identifies a tuple.

• A candidate key is a minimal superkey.

• One candidate key is selected as the primary key.

• Foreign keys connect child values to existing parent keys.

• Domain, entity and referential constraints reject different kinds of bad state.

For a 10-minute self-check, add Instructor and CourseID -> Instructor to REGISTRATION. Both candidate keys remain unchanged because each already contains CourseID, so each closure gains Instructor automatically. For a second drill, redeclare ENROLMENT's foreign key to COURSE with ON DELETE CASCADE and re-check the OS202 deletion: it now succeeds and takes enrolment (101, 'OS202', '2026S1') with the course, while ON DELETE RESTRICT on the department reference still blocks the delete of department 10.

For a full subject-wise sequence, continue with GATE Guidance by Sanchit Sir. For timed DBMS practice, use the GATE Test Series and apply both checks before reading the solution.