Primary Key Constraint and Check Constraint August 27, 2014SQL Servercheck constraint, Check Constraint Sql Server 2012, Check Constraint Tsql, primary key constraint, PRIMARY KEY CONSTRAINT AND CHECK CONSTRAINT, Primary Key Constraint Sql Server 2012, primary key sql, sql server check constraint, sql server primary key
Primary Key Constraint and Check Constraint In this tutorial we are going to explain how you can create a primary key and a check the constraint. This tutorial is meant for beginners. We are not going to explain the whole syntax of these constraints.
Primary key constraint is used to uniquely identify each row in the table. A table can only have one primary key constraint and the column that uses the primary key constraint cannot accept null values or duplicate values. The primary key constraint is used to enforce data integrity in a table. While the check constraint it is used to enforce domain integrity by limiting the values you can enter in the column.
PRIMARY KEY CONSTRAIN When you create a table, you can declare the primary key constraint in the column definition. CREATE TABLE PRODUCTS ( PRODUCT_ID INT CONSTRAINT PRODUCT_ID_CONSTRAINT PRIMARY KEY IDENTITY(1,1), NAME NVARCHAR(60) NOT NULL, [DESCRIPTION] NVARCHAR(MAX) NOT NULL, ) Tip! It is considered a good practice to name your constraints with a short and readable name. Another way of declaring a primary key constraint in your table is to declare it at the end of your table. CREATE TABLE PRODUCTS1 ( PRODUCT_ID INT IDENTITY(1,1), NAME NVARCHAR(60) NOT NULL, [DESCRIPTION] NVARCHAR(MAX) NOT NULL, CONSTRAINT PRODUCT_ID_CONSTRAINT PRIMARY KEY(PRODUCT_ID) ) Tip! You can use the combination of unique constraints to replace the primary key constraint. CREATE TABLE PRODUCTS ( PRODUCT_ID INT UNIQUE NOT NULL IDENTITY(1,1), NAME NVARCHAR(60) NOT NULL, [DESCRIPTION] NVARCHAR(MAX) NOT NULL ); If you omitted to add a primary key constraint or you didn’t want to declare a constraint when you created a table, that’s not a problem; you can alter the table and add a primary key constraint.
Adding a Primary key constraint by altering/modifying the table. ALTER TABLE PRODUCTS ADD CONSTRAINT PRODUCT_ID_CONSTRAINT PRIMARY KEY(PRODUCT_ID) GO
Deleting a Primary key constraint by altering/modifying the table. In order to delete a Primary key you can alter the table then use the "Drop" statement. Another way of deleting it is to use “Object Explorer”. ALTER TABLE PRODUCTS DROP CONSTRAINT PRODUCT_ID_CONSTRAINT
Checking for existence of a primary key constraint. If you wonder if there is a way you can check if the table has a primary key constraint, I can assure you there is. A way of checking is by using “Catalog views” SELECT * FROM sys.key_constraints GO An alternative way to the first one is to go to the “Object Explorer” and select the table you want to check. If it has a constraint, then select the key folder.
Primary key constraint object explorer
CHECK CONSTRAINT Let’s begin explaining the check constraint by creating a table that uses a check constraint.
CREATE TABLE PRODUCTS1 ( PRODUCT_ID INT PRIMARY KEY IDENTITY(1,1), NAME NVARCHAR(60) NOT NULL, [DESCRIPTION] NVARCHAR(MAX) NOT NULL, Price money constraint check_money check(price >0) ); Tip! A table can have multiple check constraints.
Adding a Primary Key Constraint by Altering/Modifying the Table If you want to add a new constraint on the same column after you have created the table, then you can alter the table and add a check constraint like in the following code. ALTER TABLE PRODUCTS1 ADD CONSTRAINT MONEY_CHECK CHECK (PRICE <100); GO
Deleting a Primary key constraint by altering/modifying the table. If you want to delete a constraint, you can delete it from “Object Explorer” or you can use T-SQL. To delete a check constraint using T-SQL use the following code: ALTER TABLE PRODUCTS1 DROP CONSTRAINT CHECK_MONEY Another alternative to “Catalog views” would be the “Object Explorer” .
Checking for Existence of a Check Constraint When we explained the primary key constraint, we mentioned that sometimes you need to check if the table has a constraint already. To do that we used the “Catalog Views” or we used the “Object Explorer”. The same works for check constraint. However, you have to use different “Catalog Views” and a different folder in “Object Explorer”.
Check constraint There is another way of finding information about check constraints. It is INFORMATION_SCHEMA.CHECK_CONSTRAINTS but we will include that in another tutorial.
Learn more at http://www.pcfixtutorials.com