Introduction
For many businesses, their data is the backbone of their operation. It can be used for analysis, decision making, and communication between staff members. Without a strong understanding of what Data Integrity means, businesses may not be able to effectively manage their data or protect it in case something happens to it. DBAs and data developers must pay high attention to data integrity. This article is for DBAs and data developers who work with SQL Server. In this article we'll discuss what Data Integrity is, why it's important, and how we can make sure to maintain and implement Data Integrity in SQL Server.
Data Integrity in SQL
Data Integrity is used to maintain the accuracy and consistency of data in a table.
Classification of Data Integrity
- System/Pre-Defined Integrity
- User-Defined Integrity
System/Pre-Defined Integrity
We can implement this using constraint. This is divided into three categories.
Entity Integrity
Entity integrity ensures each row in a table is a uniquely identifiable entity. We can apply Entity integrity to the Table by specifying a primary key, a unique key, and not null.
Referential Integrity
Referential integrity ensures the relationship between the Tables.
We can apply this using a Foreign Key constraint.
Domain Integrity
Domain integrity ensures the data values in a database follow defined rules for values, range, and format. A database can enforce these rules using Check and Default constraints.
Constraints
Constraints are used for enforcing, validating, or restricting data. Constraints are used to restrict data in a Table.
Constraints in SQL Server
Default
Default Constraint is used to assign the default value to a particular column in the Table.
By using this constraint we can avoid the system-defined value from a column while the user inserts values in the Table.
A Table can contain any number of default constraints.
Default constraints can be applied to any datatypes.
<head id="ctl00_Head1"><title>
C# Corner : Create Blog
</title><link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Open+Sans:300,400,700&display=swap"><link href="/App_Themes/CSharp/csharp_20230624.css" rel="stylesheet">
<link href="/App_Themes/CSharp/responsive_20230624.css" rel="stylesheet">
<link href="/App_Themes/CSharp/AjaxCalender_16052015.css" rel="stylesheet">
<link href="/jQuery/datepickerUI/jquery-ui.autocomplete.min.css" rel="stylesheet">
<link href="/App_Themes/CSharp/new_design_20230624.css" rel="stylesheet">
<link rel="Shortcut Icon" href="/Images/favicon.png">
<script src="/JQuery/jquery.cookie.js"></script><script src="/JQuery/jquery-Tabs.min.js"></script><script src="/JavaScripts/print_content.js"></script><script src="/JQuery/jquery.min-20150912.js" type="text/javascript"></script>
<script type="text/javascript">
var $j = jQuery.noConflict();
</script>
<meta name="viewport" content="width=device-width, initial-scale=1, user-scalable=no"><script>if(!/localhost|test\.c-sharpcorner/.test(window.location.hostname)){var _gaq=_gaq||[];_gaq.push(["_setAccount","UA-535360-1"]),_gaq.push(["_trackPageview"]),function(){var t=document.createElement("script");t.type="text/javascript",t.async=!0,t.src="http://www.google-analytics.com/ga.js";var e=document.getElementsByTagName("script")[0];e.parentNode.insertBefore(t,e)}()}</script>
<style>
.cse .gsc-search-button-v2, .gsc-search-button-v2{padding:6px 10px!important;}
.gsc-search-box-tools .gsc-search-box .gsc-input{padding-right: 0!important;background: #fff;}
.gsc-search-button{margin-left:0!important;}
.gsc-search-button-v2, .gsc-search-button-v2:hover, .gsc-search-button-v2:focus{border-radius:0!important;border:0!important;}
td.gsc-search-button{background:#f60;}
.gsc-resultsbox-visible{float:left!important;width:60%!important}
.gsc-adBlock{float:right!important;width:40%!important}
.gsc-url-top{display:none!important;}
.gsc-input{height: 22px!important;}
input.gsc-input, .gsc-input-box, .gsc-input-box-hover, .gsc-input-box-focus {border: none!important;}
.sticky-ad.fixed-t{position: fixed; top: 47px; right: auto; display: flex; justify-content: center; align-content: center; width: 100%; max-width: 396.66px;}
.sticky-ad.fixed-b{position:fixed;bottom:280px; right: auto;display: flex; justify-content: center; align-content: center; width: 100%; max-width: 396.66px;}</style>
<script>
function loadFont(url) {
const head = document.getElementsByTagName('head')[0];
const lnk = document.createElement('link');
lnk.rel = "stylesheet";
lnk.href = url;
head.appendChild(lnk);
}
loadFont('https://fonts.googleapis.com/css?family=Oswald&display=swap')
</script><link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Oswald&display=swap">
<style> li.box_sizing_bb.search_result {color: black;}</style>
<link href="/WebResource.axd?d=uC7AhM436-m6X5yV4Bb9Cjv1U_P3eIioiwi3l-VRbSeFwDijCDiu5iPHPer1K_BA85TMd_67OLml5lKWBAT43afrYCKGlm7yc2Kp34StPzHwoDtp4VvPImqGaA_0O2zCESJGb_p1-OeMNhPwV4aCHtWHbnIQPpmkXGxoxZi7nd9GRSSE0&t=637925328520000000" type="text/css" rel="stylesheet"><link href="/WebResource.axd?d=GXEYwDCnPxT9YFZthU9XsCVsYOdNQbGcjZe4e0L-IfpgArW7ap7aaY2Cd_I65XbHqj6tvFp0M5SNfEBWGV8kjnOWrELJIdbhA6xL-hC7iu5ggUWRUcDYWVL_5tOEyV0HM3sQgI6V6bWXGT5kn65KKyPbLCk1&t=637925328520000000" type="text/css" rel="stylesheet"><style>.cke{visibility:hidden;}</style><style type="text/css"> .scrollToTop{padding:10px 12px;position:fixed;bottom:8px;right:8px;display:none;background:#0086dc;z-index:999;-webkit-transition:all 0.3s;-moz-transition:all 0.3s;-o-transition:all 0.3s;transition:all 0.3s;} .scrollToTop:hover{background:#252525;}</style><script type="text/javascript" src="http://localhost/ckeditor/config.js?t=JB9C"></script><link rel="stylesheet" type="text/css" href="http://localhost/ckeditor/skins/moono-lisa/editor.css?t=JB9C"><script type="text/javascript" src="http://localhost/ckeditor/lang/en.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/styles.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/codesnippet/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/prism/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/justify/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/tableresize/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/colorbutton/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/panelbutton/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/font/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/imageautosize/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/markdown/plugin.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/youtube/plugin.js?t=JB9C"></script><link rel="stylesheet" type="text/css" href="http://localhost/ckeditor/plugins/scayt/skins/moono-lisa/scayt.css?t=JB9C"><link rel="stylesheet" type="text/css" href="http://localhost/ckeditor/plugins/scayt/dialogs/dialog.css?t=JB9C"><link rel="stylesheet" type="text/css" href="http://localhost/ckeditor/plugins/tableselection/styles/tableselection.css"><link rel="stylesheet" type="text/css" href="http://localhost/ckeditor/plugins/wsc/skins/moono-lisa/wsc.css?t=JB9C"><script type="text/javascript" src="http://localhost/ckeditor/plugins/codesnippet/lang/en.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/colorbutton/lang/en.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/font/lang/en.js?t=JB9C"></script><script type="text/javascript" src="http://localhost/ckeditor/plugins/youtube/lang/en.js?t=JB9C"></script><link rel="stylesheet" type="text/css" href="http://localhost/ckeditor/plugins/dialog/styles/dialog.css"><script type="text/javascript" src="http://localhost/ckeditor/plugins/prism/lib/prism/prism_patched.min.js"></script><style>.cke_button__markdown_label {display: inline;}</style><script type="text/javascript" src="http://localhost/ckeditor/plugins/codesnippet/dialogs/codesnippet.js?t=JB9C"></script><link rel="stylesheet" type="text/css" href="http://localhost/ckeditor/skins/moono-lisa/dialog.css?t=JB9C"></head>
Example
Create table Demo(Id int,name varchar(50),Salary int default 15000)