Home  >  Blog  >   SQL Server

SQL Server Constraints with Example

Rating: 5
  
 
4028

SQL Server Constraints with Example

  • Constraints are used to prevent invalid data entry into our tables.
  • Generally constraints are created on table columns.

In all Databases all above constraints are defined into two ways

(i) Column level
(ii) Table level

If you want to build your career with a SQL Server certified professional, then visit Mindmajix - A Global online training platform: “SQL Server Training”  Course.  This course will help you to achieve excellence in this domain.

(i) Column level:- 

In this method we are defining constraints in individual columns i.e. whenever we are defining column than only we are specifying constraints type.

Syntax:-

Create table tablename (col1 datatype (size) constraints type, col2 datatype (size) constraints type, _ _ _  )

(ii) Table level:-

In this method we are defining constraints on group of columns i.e. first we specifying all columns and last only we’re specifying constriants type, alsong with group of columns.

Syntax:-

Create table tablename (col1 datatype (size), col2 datatype (size), _ _ _ _ constraint type (col1, col2, ….) );

Oracle server having following types of contraints –

1. Not null.

2. Unique

3. Primary Key

4. Foreign Key

5. Check

 MindMajix YouTube Channel

1. NOT NULL:-

  • In all databases NOT NULL doesn’t support table level.
  • NOT NULL doesn’t accept Null value but it accepts duplicate values.

Column level:-

Eg :-

SQl > create table z1 (sno number (10) not null, name varchar2);
SQL > insert table z1 values (null, ‘a’);
Error : cannot insert Null into sno
SQL > insert table z1 values (1, ‘X’);
SQL > insert table z1 values (1, ‘y’);

O/P:  

S.NoName
1X
1Y

2. Unique:-

  • In all databases unique constraints defined on column level, table level.
  • Unique constraints doesnot accept duplicate value but accept Null values.

Note:- Whenever we are creating unique constrains internally oracle server automatically creates B-free indexes for those columns.

Column level:-

SQL > create table z2 (sno number (10) unique, name varchar2(10));

Table level:-

SQL > create table z3 (sno number (10), name varchar2(10), unique (sno, name));
SQL > select * from z3;

O/P:  

S.NoName
1Sarika
1abc

SQL > insert into z3 values (1, ‘abc’)

Error : unique constraints violated

Check Out SQL Server Tutorials

3. Primary Key:-

  • Primary key uniquely identifying a record in a table, in all databases their can be only one primary key in a table and also primary key doesn’t accept duplicate values, null values.
  • Whenever we are creating B-free indexes on those columns.

Column Level:-

SQL > create table z4 (Sno number (10) primary key, name vaschar2(10));

Table Level:-

SQL > create table z5 (Sno number (!0), name varchar2(10), Primary key (sno, name));

This is also called as composite primary key i.e. it is a combination of column as a single primary key.

4. Foreign Key:-

  • In all databases if you want to establishes relationship between tables then we are using referencial integrity constraints foreign key.
  • One table foreign key must belong to another table primary key and also these two columns must belongs to same datatype.
  • Always foreign key values always based on primary key values only.
  • Generally primary key doesn’t accepts duplicate, Null values where as foreign key accepts duplicate, null values.

Column level (References):-

Syntax:-

SQL > create table tablename (col1 datatype (size) references master tablename (primary key col name));

SQL > create table w4 (sno number (!0) references z4);

Table Level:- (Foreign key, references)

Syntax:-

create table tablename (col1 datatype (size), col2 datatype (size), …..
                         Foreign key (col1, col2,…)
                         References
                         Mastertablename(primary key colnames));

Eg:-

SQL > create table z6 (sno number (10), name varchar2(10); Foreign key (sno, name) references z5);

Whenever we are establishing relationship between table by using Foreign key then oracle server automatically violet following two rules; these are :-

A. Deletion in master table.
B. Insertion in child table.

A. Deletion in master table

When we are trying to delete a master table record in master table if the record exists in child table then oracle server returns an error ora – 2292. To overcome this problem if we want to delete master table record in master table then first we must to delete child table record in child table then only we are allow to delete those record in master table otherwise use an on delete cascade clause.

On delete cascade:-

This clause is used along with foreign key only. Whenever we are specifying this clause in child table then we are deleting a master table record within master table then automatically the record is deleted in master table and those records are automatically deleted in child table.

Syntax:-

Create table tablename (col1 datatype(size) references mastertable name (primary key col name) on delete cascade, ……);

TESTING (DELETION IN MASTER TABLE)

SQL > delete from mas where Sno=1;

One row deleted

SQL > select * from mas;

O/P: 

S.No
2
3

SQL > select * from child;

O/P:

S.No
2
2
3

On delete set null:-

Oracle also supports another clause along with foreign key on delete set null. Whenever we use this clause, whenever we are deleting primary key value in master table then automatically that record is deleted in master table and corresponding foreign key values are automatically set to null in child table.

Syntax:-

Create table tablename (col1 datatype (size) references mastertablename (primary key colname) on delete set null…… );

B. Insertion in child table:-

When we are try to insert otherthan primary key values into foreign key then oracle server returns an error ora-2291 because in all databases always foreign key values based on primary key values only.

Note:-

Generally when we are truncating master table by using “truncate table tablename” than DB servers returns error to overcome this problem oracle 12C introduced. Cascade clause along with truncate table tablename.

Syntax:-

Truncate table mastertablename cascade;
Before we are using this command we must use on delete cascade class along with foreign key.

Frequently Asked SQL Server Interview Questions

5. Check:-

  • Check constrains are used to define logical condition according to client business rules.
  • In oracle check constraints doesnot work with sys Date Function.

Column Level:-

Syntax-

SQL > Create table tablename (col1 datatype ( size) check (logical condition) col2 datatype (size), …..);

Eg -1:-  

    SQL > create table test (sal number (10) check (sal > 2000));
    SQL > insert into test values (1000);
                           Error:
    SQL > insert into test values (5000);
               1 row created
     SQL > select * from test;

SAL
5000

Ex – 2:-

     SQL > create table test1 (name vachar2(10) check (name = upper (name)));
     SQL > insert into test1 values (‘abc’);
                     Error:
     SQL > insert into test1 values (‘ABC’);
               1 row created
     SQL > select * from test1;

O/P: 

NAME
ABC

Table level:-

Syntax:-

Create table tablename (colname, datatype (size), colname2 datatype (size) check (cond1, cond2, ….));

Eg:-

SQL > create table test1 (name varchar2(10), sal number (10), check (sal > 2000 and name = upper (name)));

Assign user defined names to constraints
(OR)
Constraints Names

In all DB whenever we are creating constraints then DB server automatically converts on unique identification no for identifying a constraints auniquely, oracle server also automatically generates an unique identification number in the format of sys_cn  This is called Predefined constraint name in place any number of this one we can also create our own name by using constraints keyword this is called userdefined constraint name.

Syntax:-
Contraints
 

Ex:-        Predefined constraint name:-

              SQL > create table test (sno number (10) primary key);

              SQL > insert into test values (1);   

              SQL > insert into test values (1);    X

Error: Unique constraints

(SCOTT_SYS (005571)) violated.

Ex:-  User defined constraint name:-

SQL > create table test1 (sno number (10) constraint P_sno primary key);

SQL > insert into test1 values (1); 

SQL > insert into test1 values (1);

    Error: Unique constraints

                       (SCOTT_P.SNO) violated.

  User.defined constraint name.

List of Related Microsoft Certification Courses:

 SSRS Power BI
 SSAS SQL Server
 SCCM SQL Server DBA
 SharePoint BizTalk Server
 Team Foundation Server BizTalk Server Administrator

 

Join our newsletter
inbox

Stay updated with our newsletter, packed with Tutorials, Interview Questions, How-to's, Tips & Tricks, Latest Trends & Updates, and more ➤ Straight to your inbox!

Course Schedule
NameDates
SQL Server TrainingMar 30 to Apr 14View Details
SQL Server TrainingApr 02 to Apr 17View Details
SQL Server TrainingApr 06 to Apr 21View Details
SQL Server TrainingApr 09 to Apr 24View Details
Last updated: 04 Apr 2023
About Author

Arogyalokesh is a Technical Content Writer and manages content creation on various IT platforms at Mindmajix. He is dedicated to creating useful and engaging content on Salesforce, Blockchain, Docker, SQL Server, Tangle, Jira, and few other technologies. Get in touch with him on LinkedIn and Twitter.

read more