SQL Constraints

SQL Constraints

SQL constraints are used to specify rules for the data in a table.  If there is any violation between the constraint and the data action, the action is aborted by the constraint.

Syntax for SQL Constraints

SQL CREATE TABLE + CONSTRAINT Syntax

CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
);

In SQL, we have the following constraints

NOT NULL - Indicates that a column cannot store NULL value UNIQUE - Ensures that each row for a column must have a unique value PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table CHECK - Ensures that the value in a column meets a specific condition DEFAULT - Specifies a default value when specified none for this column

Column Exists or Not in SQL Server

Column Exists or Not in SQL Server

CREATE FUNCTION Axfn_ColumnExists
(
 @TableName VARCHAR(100) ,
@ColumnName VARCHAR(100) )
 RETURNS VARCHAR(100)
AS
BEGIN
 DECLARE @Result VARCHAR(100);
 IF EXISTS
(
 SELECT 1 FROM INFORMATION_SCHEMA.Columns
             WHERE TABLE_NAME = @TableName AND COLUMN_NAME = @ColumnName ) BEGIN
 SET @Result = 'Already Exsits'
 END
 ELSE
 BEGIN SET @Result = 'Not Available, You can create now!'
 END
 RETURN (@Result)
 END
GO