{"id":22523,"date":"2018-08-10T06:15:52","date_gmt":"2018-08-10T06:15:52","guid":{"rendered":"https:\/\/data-flair.training\/blogs\/?p=22523"},"modified":"2021-03-10T20:42:41","modified_gmt":"2021-03-10T15:12:41","slug":"sql-constraint","status":"publish","type":"post","link":"https:\/\/data-flair.training\/blogs\/sql-constraint\/","title":{"rendered":"SQL Constraint Types, Syntax and Examples"},"content":{"rendered":"

We have large collections of data in our databases, and to maintain the correctness of the data, we require logical checks on the data. In SQL, we have constraints as the logical checks for the data.<\/p>\n

Constraints are important to avoid ambiguity in data. We can put constraints on the whole table or only the columns depending on how we want to manage our data.<\/p>\n

They are the rules made to keep up the integrity and correctness of data. When we use constraints, we put a check on our data, which helps us to follow a specified pattern.<\/p>\n

For example: When we deal with the data of a bank account, we need to have a constraint that would help us to uniquely identify all the customers associated with the bank.<\/p>\n

Thus, we require constraints to meet all these industrial requirements. For the use case of a bank, we will be using the UNIQUE constraint to keep the identification number or the account number different to each user of the banking system.<\/p>\n

What are the Constraints and Use of Constraints?<\/h3>\n

Constraints are rules which we apply to the whole table or the columns to restrict what and how the data is put in the database.<\/p>\n

We use constraints to maintain the effectiveness and integrity of our data. By using constraints, we can keep our data free from ambiguity and unwanted errors. Constraints are of two types:<\/p>\n

    \n
  1. Table Level Constraints:<\/strong> These constraints are applicable to the whole table.<\/li>\n
  2. Column Level Constraints:<\/strong> These constraints are applicable to the data stored in the column.<\/li>\n<\/ol>\n

    Syntax:<\/strong><\/p>\n

    CREATE TABLE tableName (\r\n  columnName1 datatype constraint1,\r\n  columnName2 datatype constraint2,\r\n  columnName3 datatype constraint3,\r\n  ....\r\n);\r\n<\/pre>\n
    Types of Data Constraints<\/h3>\n
    \"Types<\/a><\/p>\n
    1. Input\/Output Constraints:<\/strong> We use input and output constraints to check the speed of insertion, extraction, and deletion. Example: Primary Key, Foreign Key, etc.<\/p>\n
    2. Business Constraints:<\/strong> These are applicable on the data before the data is tucked in the table. Example: Unique, Null, etc.<\/p>\n
    Different SQL Constraints and Usage<\/h3>\n\n\n\n\n\n\n\n\n\n\n
    Sr.No<\/b><\/td>\nConstraint in SQL<\/b><\/td>\nUsage<\/b><\/td>\n<\/tr>\n
    1<\/span><\/td>\nNOT NULL<\/span><\/td>\nUsed when the column should not take null values.<\/span><\/td>\n<\/tr>\n
    2<\/span><\/td>\nUNIQUE<\/span><\/td>\nUsed when all the values in the column need to be different.<\/span><\/td>\n<\/tr>\n
    3<\/span><\/td>\nPRIMARY KEY<\/span><\/td>\nUsed so that each row in a table has a unique identity.<\/span><\/td>\n<\/tr>\n
    4<\/span><\/td>\nFOREIGN KEY<\/span><\/td>\nUsed to identify rows or records stored in another table.<\/span><\/td>\n<\/tr>\n
    5<\/span><\/td>\nCHECK<\/span><\/td>\nUsed to ensure that all the data in the column satisfies some given condition.<\/span><\/td>\n<\/tr>\n
    6<\/span><\/td>\nDEFAULT<\/span><\/td>\nUsed to set the default value for the column when no value is put by the user.<\/span><\/td>\n<\/tr>\n
    7<\/span><\/td>\nINDEX<\/span><\/td>\nUsed to retrieve and search data in a database quickly.<\/span><\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n
    Examples of SQL Constraint<\/h3>\n
    1. Use of NOT NULL and UNIQUE Constraint in SQL<\/h4>\n
    Query:<\/strong><\/p>\n
    CREATE TABLE DataFlair_Emp\r\n(\r\nID int(6) NOT NULL UNIQUE,\r\nNAME varchar(10),\r\nemail varchar(20)\r\n);\r\n<\/pre>\n
    \"SQL<\/a><\/p>\n
    2. Using PRIMARY and FOREIGN Key Constraint in SQL<\/h4>\n
    Query:<\/strong><\/p>\n
    CREATE TABLE DataFlair_1\r\n(\r\nID int NOT NULL,\r\nORDER_NO int,\r\nC_ID int,\r\nPRIMARY KEY (ID),\r\nFOREIGN KEY (ID) REFERENCES DataFlair_2(ID)\r\n)\r\n<\/pre>\n
    \"Example<\/a><\/p>\n
    3. Usage of CHECK and DEFAULT Constraint in SQL<\/h4>\n
    Query:<\/strong><\/p>\n
    CREATE TABLE DataFlair_3\r\n(\r\nID int(6) NOT NULL,\r\nNAME varchar(10) NOT NULL,\r\nAGE int NOT NULL CHECK (AGE >= 18),\r\nCompany varchar(20) DEFAULT 'DataFlair'\r\n);\r\n<\/pre>\n
    \"SQL<\/a><\/p>\n
    Summary<\/h3>\n
    SQL constraints are beneficial to store data in an orderly fashion in the database. We have been discussing the SQL constraints and examples.<\/p>\n
    Constraints are important to maintain the integrity and effectiveness of the data we have in our database over time. Using SQL, we store data in such a fashion that it is free from ambiguities and unwanted errors.<\/p>\n
    Constraints like the Primary key and Foreign key make it easy for us to retrieve data and check-in multiple tables for the same. The check constraint helps us to keep the columns free from null values.<\/p>\n
    When null values are put in a database unwanted errors creep in, causing logical and mathematical errors.<\/p>\n
    By proper usage of constraints, we can keep our data error-free and ensure that all the logical and mathematical operations are yielding proper results.<\/p>\n","protected":false},"excerpt":{"rendered":"
    We have large collections of data in our databases, and to maintain the correctness of the data, we require logical checks on the data. In SQL, we have constraints as the logical checks for...<\/p>\n","protected":false},"author":6,"featured_media":86836,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[66],"tags":[2922,3707,4066,9131,13401,13419,13451,13460,13512,13538,13540],"class_list":["post-22523","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-sql","tag-constraint-sql","tag-default-constraint-in-sql","tag-drop-constraint-sql-server","tag-not-null-sql-constraints","tag-sql-add-constraint","tag-sql-constraint-example","tag-sql-drop-constraint","tag-sql-foreign-key-constraint","tag-sql-primary-key","tag-sql-server-check-constraint","tag-sql-server-constraint"],"yoast_head":"\nSQL Constraint Types, Syntax and Examples - DataFlair<\/title>\n<meta name=\"description\" content=\"SQL constraint types with synatx and exmaples - Not Null, Unique, Primary key, Foreign key, Check, default, Index etc.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/data-flair.training\/blogs\/sql-constraint\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"SQL Constraint Types, Syntax and Examples - DataFlair\" \/>\n<meta property=\"og:description\" content=\"SQL constraint types with synatx and exmaples - Not Null, Unique, Primary key, Foreign key, Check, default, Index etc.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/data-flair.training\/blogs\/sql-constraint\/\" \/>\n<meta property=\"og:site_name\" content=\"DataFlair\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/DataFlairWS\/\" \/>\n<meta property=\"article:published_time\" content=\"2018-08-10T06:15:52+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2021-03-10T15:12:41+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Constraints.jpg\" \/>\n\t<meta property=\"og:image:width\" content=\"1200\" \/>\n\t<meta property=\"og:image:height\" content=\"628\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"DataFlair Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:site\" content=\"@DataFlairWS\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"DataFlair Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"SQL Constraint Types, Syntax and Examples - DataFlair","description":"SQL constraint types with synatx and exmaples - Not Null, Unique, Primary key, Foreign key, Check, default, Index etc.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/data-flair.training\/blogs\/sql-constraint\/","og_locale":"en_US","og_type":"article","og_title":"SQL Constraint Types, Syntax and Examples - DataFlair","og_description":"SQL constraint types with synatx and exmaples - Not Null, Unique, Primary key, Foreign key, Check, default, Index etc.","og_url":"https:\/\/data-flair.training\/blogs\/sql-constraint\/","og_site_name":"DataFlair","article_publisher":"https:\/\/www.facebook.com\/DataFlairWS\/","article_published_time":"2018-08-10T06:15:52+00:00","article_modified_time":"2021-03-10T15:12:41+00:00","og_image":[{"width":1200,"height":628,"url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Constraints.jpg","type":"image\/jpeg"}],"author":"DataFlair Team","twitter_card":"summary_large_image","twitter_creator":"@DataFlairWS","twitter_site":"@DataFlairWS","twitter_misc":{"Written by":"DataFlair Team","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/#article","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/"},"author":{"name":"DataFlair Team","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89"},"headline":"SQL Constraint Types, Syntax and Examples","datePublished":"2018-08-10T06:15:52+00:00","dateModified":"2021-03-10T15:12:41+00:00","mainEntityOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/"},"wordCount":590,"commentCount":2,"publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Constraints.jpg","keywords":["constraint SQL","default constraint in SQL","drop constraint sql server","NOT NULL SQL constraints","SQL add constraint","SQL constraint Example","SQL drop constraint","SQL foreign key constraint","SQL Primary Key","SQL server check Constraint","SQL Server Constraint"],"articleSection":["SQL Tutorials"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/data-flair.training\/blogs\/sql-constraint\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/","url":"https:\/\/data-flair.training\/blogs\/sql-constraint\/","name":"SQL Constraint Types, Syntax and Examples - DataFlair","isPartOf":{"@id":"https:\/\/data-flair.training\/blogs\/#website"},"primaryImageOfPage":{"@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/#primaryimage"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/#primaryimage"},"thumbnailUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Constraints.jpg","datePublished":"2018-08-10T06:15:52+00:00","dateModified":"2021-03-10T15:12:41+00:00","description":"SQL constraint types with synatx and exmaples - Not Null, Unique, Primary key, Foreign key, Check, default, Index etc.","breadcrumb":{"@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/data-flair.training\/blogs\/sql-constraint\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/#primaryimage","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Constraints.jpg","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2018\/08\/SQL-Constraints.jpg","width":1200,"height":628,"caption":"SQL Constraints"},{"@type":"BreadcrumbList","@id":"https:\/\/data-flair.training\/blogs\/sql-constraint\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Blog Home","item":"https:\/\/data-flair.training\/blogs\/"},{"@type":"ListItem","position":2,"name":"Searching for Groups of Scala Regex","item":"https:\/\/data-flair.training\/blogs\/tag\/searching-for-groups-of-scala-regex\/"},{"@type":"ListItem","position":3,"name":"SQL Constraint Types, Syntax and Examples"}]},{"@type":"WebSite","@id":"https:\/\/data-flair.training\/blogs\/#website","url":"https:\/\/data-flair.training\/blogs\/","name":"DataFlair","description":"Learn Today. Lead Tomorrow.","publisher":{"@id":"https:\/\/data-flair.training\/blogs\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/data-flair.training\/blogs\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/data-flair.training\/blogs\/#organization","name":"DataFlair","url":"https:\/\/data-flair.training\/blogs\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/","url":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","contentUrl":"https:\/\/data-flair.training\/blogs\/wp-content\/uploads\/sites\/2\/2016\/07\/Data-Flair.png","width":106,"height":48,"caption":"DataFlair"},"image":{"@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/DataFlairWS\/","https:\/\/x.com\/DataFlairWS","https:\/\/www.linkedin.com\/company\/dataflair-web-services-pvt-ltd\/","https:\/\/www.youtube.com\/user\/DataFlairWS"]},{"@type":"Person","@id":"https:\/\/data-flair.training\/blogs\/#\/schema\/person\/2c58ecb4f73a39f0ef993f1ddfcd7b89","name":"DataFlair Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","url":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/1ce4a0e3e542444fc73bbebf83e89e8b73e2d95ccb1fcee64da9945f078b97c5?s=96&d=mm&r=g","caption":"DataFlair Team"},"description":"The DataFlair Team provides industry-driven content on programming, Java, Python, C++, DSA, AI, ML, data Science, Android, Flutter, MERN, Web Development, and technology. Our expert educators focus on delivering value-packed, easy-to-follow resources for tech enthusiasts and professionals.","url":"https:\/\/data-flair.training\/blogs\/author\/dfteam2\/"}]}},"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/22523","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/users\/6"}],"replies":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/comments?post=22523"}],"version-history":[{"count":5,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/22523\/revisions"}],"predecessor-version":[{"id":86841,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/posts\/22523\/revisions\/86841"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media\/86836"}],"wp:attachment":[{"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/media?parent=22523"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/categories?post=22523"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/data-flair.training\/blogs\/wp-json\/wp\/v2\/tags?post=22523"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}