Constraints In SQL
Constraints are the rules which are enforced on columns of the table in database. They specifically used to limit or restrict that data goes into a column. Constraints ensure the reliability and accuracy of data.
We can apply constraints on column level or table level. In this column level constraints are applied on one column at a time but when you use table-level constraints these are applied on all columns of that table.
Commonly used Column constraints are below
- Not Null
- Primary Key
- Foreign key
Commonly used table-level constraints
- Primary key
- Foreign key
In case if you insert any data that violets the constraints then the operation is aborted.
Constraints can be applied at the time of table creation by using Create Table syntax and another is with Alter table statement.
This constraint is used to set or specify a default value for that column if any value doesn’t provide. This means it is used to insert a default value into a column. The default value is set for all records if any value doesn’t provide including Null.
To alter an existing table or add default constraint to a column using Alter table syntax.
Alter table table_name Add constraint constraint_name Default default_value for Column_name
To add a new column to an existing table with a default value
Alter table table_name Add column_name data_type (Null | Not Null) Constraint constraint_name default default_value
Alter table table_name Drop constraint constraint_name
alter table Person add constraint df_value default 3 for [genederID]
The check constraint is used to limit the range of the values which are entered for a specific column. In our case, a person’s table is already created. So we can add a new column age with check constraint by using alter table syntax.
alter table person add age int constraint chk_age check(age>0 and age<200)
Now if I going to insert negative value in that person column by using below query
insert into Person values(7,'rr1','email@example.com',Null,-9)
it will throw below error Msg 547, Level 16, State 0, Line 50 The INSERT statement conflicted with the CHECK constraint “chk_age”. The conflict occurred in database “temp”, table “dbo.Person”, column ‘age’. The statement has been terminated. The only flaw of this if you pass the null value it will be inserted any way and not throw any error.
insert into Person values(9,'rr2','firstname.lastname@example.org',Null,NULL)
this query works perfectly.
How Check constraint works?
When we add check constraint we add some condition in parenthesis. It is actually Boolean Expression when we pass value it will first pass to that expression and the expression returns the value.
If it returns true value then check constraint allows the value otherwise it doesn’t allow that value. So what happens when we pass the null value? In this person’s age case when we pass NULL value it passes to expression and expression is evaluated this as Unknown so for that reason it allows null value.
Drop a Check Constraint Alter table person Drop constraint chek_constraint_name
This unique key constraint is used to enforce the uniqueness of a column i.e column shouldn’t allow any duplicate value. You can add unique key constraints by using a designer or by using a query.
Below is the syntax for add unique constraint by using alter table syntax.
Alter table table_name Add constraint constraint_name unique(column_name);
If you see both primary key and unique keys are used to enforce the uniqueness of column so the question in your mind when to use what?
One table has only one primary key and if you want to add uniqueness for more than one column you can use unique key constraints.
What is the difference between the Unique key and Primary Key?
- A table has only one primary key but a table can have more than one unique key
- The primary key doesn’t allow null values where a unique key allows only one null value.
alter table person add constraint unique_name_key Unique([email]);
What if you enter the same value again for a unique constraint?
insert into Person values(10,'rr2','email@example.com',Null,NULL)
I use the above query to insert value I only change a primary key value and all record is present previously in a table at 9’Th location but when I ran this query I get the following result
Msg 2627, Level 14, State 1, Line 87 Violation of UNIQUE KEY constraint ‘unique_name_key’. Cannot insert duplicate key in object ‘dbo.Person’. The duplicate key value is (firstname.lastname@example.org). The statement has been terminated.
How to drop unique key constraint?
Alter table table_name Drop constraint constraint_name;
alter table person drop constraint unique_name_key
By default, a column can contain null values but you want to restrict the column that not to allow NULL values then this constraint is used.
This not null constraint enforces a rule on a column that always contains a value.
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );
Not Null constraint by using Alter table syntax
ALTER TABLE Persons MODIFY Age int NOT NULL;
It is used to access the data very fast means for faster retrieval of data index is created. An index can be created on a single column or multiple columns. When you create an index it will create or assign rowed for each row.
Indexes have a good performance on large databases when it comes to retrieval of data but performance is low when insertion.
create table Person(ID int not null Primary key,name varchar(100),email varchar(100),genederID int)
CREATE INDEX index_name ON table_name ( column1, column2.....);
CREATE INDEX person_tabel_index ON person (id,name);
How to drop an Index?
ALTER TABLE table_name DROP INDEX index_name;
But you will get the following error
Msg 10785, Level 16, State 2, Line 97 The operation ‘ALTER TABLE DROP INDEX’ is supported only with memory-optimized tables. Msg 1750, Level 16, State 0, Line 97 Could not create constraint or index. See previous errors.
To avoid this error your table needs to memory-optimized and for that when you create a table use this syntax to create table memory optimize. But for that, your database needs to memory-optimized so use below command for that in below query temp is my database name.
ALTER DATABASE temp ADD FILEGROUP [TestDBSampleDB_mod_fg] CONTAINS MEMORY_OPTIMIZED_DATA; After that use below command ALTER DATABASE temp ADD FILE (NAME='temp_mod_dir', FILENAME='D:\timepass\TestDB_mod_dir') TO FILEGROUP [TestDBSampleDB_mod_fg]; --Then you use this create table command to create memory optimized table. CREATE TABLE userSession ( SessionId int not null, UserId int not null, CreatedDate datetime2 not null, ShoppingCartId int index ix_UserId nonclustered hash (UserId) with (bucket_count=400000) ) WITH (MEMORY_OPTIMIZED=ON, DURABILITY=SCHEMA_ONLY) ;
A primary key is a field in the table which uniquely identifies the row in a table. The primary key contains unique values. The primary key doesn’t have null values.
A primary key is one per table but it can contain more than one column and this called a Composite key. Create primary key at the time of table creation
CREATE TABLE CUSTOMERS( ID INT NOT NULL, NAME VARCHAR (20) NOT NULL, AGE INT NOT NULL, ADDRESS CHAR (25), SALARY DECIMAL (18, 2), PRIMARY KEY (ID) );
Create a primary key using the alter table
ALTER TABLE CUSTOMER ADD PRIMARY KEY (ID);
When you use alter table syntax you need to ensure that the column which you provide is Not Null.
The foreign key constraint is used to join 2 tables together.
It is a key which might have a combination of one or more column or fields in one table that refers to Primary Key in another table.
The table which contains foreign key is called the child table and the table containing the candidate key is called the referenced or parent table.
The relationship between 2 tables matches the Primary Key in one of the tables with a Foreign Key in the second table.
If a table has a primary key defined on any field(s), then you cannot have two records having the same value of that field(s).
CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) ); --Foreign key using alter table ALTER TABLE Orders ADD FOREIGN KEY (PersonID) REFERENCES Persons(PersonID); --Drop a Foreign Key constraint ALTER TABLE Orders DROP FOREIGN KEY FK_PersonOrder;