MCQ King

MS SQL Server – Integrity Constraints

Start MCQ

Integrity constraints in Microsoft SQL Server are rules or conditions that are enforced on a database’s data to ensure data accuracy, consistency, and reliability. They play a vital role in maintaining data integrity and preventing the entry of invalid or inconsistent data. Here are some common types of integrity constraints in SQL Server:

  1. Primary Key Constraint:
    • A primary key constraint ensures that a column (or set of columns) uniquely identifies each row in a table.
    • It enforces the uniqueness of values in the specified column(s) and automatically creates a unique index.
    • Primary keys are used to establish relationships between tables through foreign keys.
    • Example:CREATE TABLE Customers ( CustomerID INT PRIMARY KEY, FirstName VARCHAR(50), LastName VARCHAR(50) );
  2. Unique Constraint:
    • A unique constraint ensures that values in a column (or set of columns) are unique across all rows in a table.
    • It allows for the presence of NULL values but enforces uniqueness among non-NULL values.
    • Example:CREATE TABLE Products ( ProductID INT UNIQUE, ProductName VARCHAR(100), Price DECIMAL(10, 2) );
  3. Foreign Key Constraint:
    • A foreign key constraint establishes a relationship between two tables, ensuring referential integrity.
    • It enforces that values in a column match values in another table’s primary key or unique key.
    • It helps maintain data consistency when working with related data.
    • Example:CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, OrderDate DATE, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) );
  4. Check Constraint:
    • A check constraint defines a condition that data values in a column must satisfy.
    • It is used to ensure that data conforms to specific business rules or requirements.
    • Example:CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Salary DECIMAL(10, 2) CHECK (Salary >= 30000) );
  5. Default Constraint:
    • A default constraint specifies a default value for a column when no value is provided during an insert operation.
    • It ensures that columns have a predefined default value if not explicitly specified.
    • Example:CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE DEFAULT GETDATE() );
  6. Not Null Constraint:
    • A not null constraint ensures that a column does not contain NULL values.
    • It enforces that every row must have a value in the specified column.
    • Example:CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, FirstName VARCHAR(50) NOT NULL, LastName VARCHAR(50) NOT NULL );

Integrity constraints are a critical part of database design and help maintain data quality and consistency. By defining these constraints, you can prevent data corruption, enforce business rules, and establish relationships between tables, all of which contribute to the integrity and reliability of your database.