Constraints in SQL Server

Question

What SQL constraints and Types and when to use

Jaish Mathews · Answer

SQL constraints are foundational to creating robust and reliable databases. Here's an overview of each: SQL Constraints Constraints enforce rules on data in tables, ensuring accuracy and integrity. Common constraints include: 1. NOT NULL Purpose : Ensures a column cannot have a NULL value. When to Use : Use for columns that must always have a value (e.g., primary keys, user IDs, timestamps). Example : CREATE TABLE Employees ( EmployeeID INT NOT NULL, Name VARCHAR(50) NOT NULL ); 2. UNIQUE Purpose : Ensures all values in a column are unique. When to Use : Use for columns like email addresses, usernames, or identification numbers. Example : CREATE TABLE Users ( UserID INT NOT NULL, Email VARCHAR(100) UNIQUE ); 3. PRIMARY KEY Purpose : Combines NOT NULL and UNIQUE , uniquely identifying each row. When to Use : Use for the main identifier of a table (e.g., ID columns). Example : CREATE TABLE Orders ( OrderID INT PRIMARY KEY, OrderDate DATE NOT NULL ); 4. FOREIGN KEY Purpose : Establishes a relationship between two tables. When to Use : Use to maintain referential integrity, linking child tables to parent tables. Example : CREATE TABLE Orders ( OrderID INT PRIMARY KEY, CustomerID INT, FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID) ); 5. CHECK Purpose : Ensures values meet a specific condition. When to Use : Use for columns with specific business rules (e.g., age limits, ranges). Example : CREATE TABLE Employees ( EmployeeID INT PRIMARY KEY, Age INT CHECK (Age >= 18) ); 6. DEFAULT Purpose : Assigns a default value if none is provided. When to Use : Use for columns that need a consistent default (e.g., statuses, timestamps). Example : CREATE TABLE Products ( ProductID INT PRIMARY KEY, Status VARCHAR(20) DEFAULT 'In Stock' ); 7. INDEX Purpose : Speeds up queries on specific columns. When to Use : Use for frequently searched or sorted columns. Example : CREATE INDEX idx_lastname ON Employees (LastName); Choosing the appropriate constraint and type depends on your application's requirements. Constraints ensure data accuracy and integrity, while data types dictate how data is stored and queried efficiently.

Rajanikant Hawaldar · Answer

Hi, Please refer below articles. https://www.c-sharpcorner.com/article/an-easy-way-to-understand-constraints-in-sql-server/ https://www.c-sharpcorner.com/UploadFile/f0b2ed/constraints-in-sql-server/

Amira Bedhiafi · Answer

They are rules applied to table columns to ensure the validity, integrity, and consistency of the data. NOT NULL Constraint : a column cannot have a NULL value. For example, in an Employees table, the FirstName and LastName fields should not be NULL, as every employee must have a first and last name. UNIQUE Constraint : all values in a column are distinct. you can use it when a column must store unique values, such as an email address in a Users table. PRIMARY KEY Constraint : combination of NOT NULL and UNIQUE, you can assign it to a column or a set of columns that serve as the unique identifier for table records, like EmployeeID in an Employees table. Each table should have one primary key. FOREIGN KEY Constraint : establishes a relationship between columns in two tables, enforcing referential integrity. In other words we use it to link related data between tables. An Orders table may have a CustomerID column that references the CustomerID in a Customers table to make sure that every order is associated with a valid customer. CHECK Constraint : you can make sure that all values in a column satisfy a specific condition. For example, a Salary column can have a CHECK constraint to be within a reasonable range, such as greater than zero. DEFAULT Constraint : provides a default value for a column when none is specified. For example, you can set up a default value of GETDATE() for an OrderDate column to record the date an order was placed. SQL Constraints - W3Schools SQL Constraints - GeeksforGeeks SQL Constraints - SQL Tutorial