Primary key, Foreign Key and Default constraint are the 3 main constraints that need to be considered while creating tables or even after that. It seems very easy to apply these constraints but still we have some confusions and problems while implementing it. So I tried to write about these constraints that can be created or added at different levels and in different ways or methods.
Primary Key Constraint: Primary Keys constraints prevents duplicate values for columns and provides unique identifier to each column, as well it creates clustered index on the columns.
1)     Create Table Statement to create Primary Key
a.      Column Level
USE AdventureWorks2008 GO CREATE TABLE Products ( ProductID INT CONSTRAINT pk_products_pid PRIMARY KEY, ProductName VARCHAR(25) ); GO
b.     Table Level
CREATE TABLE Products ( ProductID INT, ProductName VARCHAR(25) CONSTRAINT pk_products_pid PRIMARY KEY(ProductID) ); GO
2)Â Â Â Â Â Alter Table Statement to create Primary Key
ALTER TABLE Products ADD CONSTRAINT pk_products_pid PRIMARY KEY(ProductID) GO
3)Â Â Â Â Â Alter Statement to Drop Primary key
ALTER TABLE Products DROP CONSTRAINT pk_products_pid; GO
Foreign Key Constraint: When a FOREIGN KEY constraint is added to an existing column or columns in the table SQL Server, by default checks the existing data in the columns to ensure that all values, except NULL, exist in the column(s) of the referenced PRIMARY KEY or UNIQUE constraint.
1)     Create Table Statement to create Foreign Key
a.      Column Level
USE AdventureWorks2008 GO CREATE TABLE ProductSales ( SalesID INT CONSTRAINT pk_productSales_sid PRIMARY KEY, ProductID INT CONSTRAINT fk_productSales_pid FOREIGN KEY REFERENCES Products(ProductID), SalesPerson VARCHAR(25) ); GO
b.     Table Level
CREATE TABLE ProductSales ( SalesID INT, ProductID INT, SalesPerson VARCHAR(25) CONSTRAINT pk_productSales_sid PRIMARY KEY(SalesID), CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID) ); GO
1)Â Â Â Â Â Alter Table Statement to create Foreign Key
ALTER TABLE ProductSales ADD CONSTRAINT fk_productSales_pid FOREIGN KEY(ProductID)REFERENCES Products(ProductID) GO
2)Â Â Â Â Â Alter Table Statement to Drop Foreign Key
ALTER TABLE ProductSales DROP CONSTRAINT fk_productSales_pid; GO
Default Constraint: Default constraint when created on some column will have the default data which is given in the constraint when no records or data is inserted in that column.
1)Â Â Â Â Â Create Table Statement to create Default Constraint
a.      Column Level
USE AdventureWorks2008 GO CREATE TABLE Customer ( CustomerID INT CONSTRAINT pk_customer_cid PRIMARY KEY, CustomerName VARCHAR(30), CustomerAddress VARCHAR(50) CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN' ); GO
b.     Table Level : Not applicable for Default Constraint
2)Â Â Â Â Â Alter Table Statement to Add Default Constraint
ALTER TABLE Customer ADD CONSTRAINT df_customer_Add DEFAULT 'UNKNOWN' FOR CustomerAddress GO
3)Â Â Â Â Â Alter Table to Drop Default Constraint
ALTER TABLE Customer DROP CONSTRAINT df_customer_Add GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
131 Comments. Leave new
Hi,
I am Ramesh from Chennai, how many tables allowed in sql server 2008.
It depends on the harddisk volume. Read about Maximum capacity specifications in SQL Server help file.
Database objects include objects such as tables, views, stored procedures, user-defined functions, triggers, rules, defaults, and constraints. The sum of the number of all objects in a database cannot exceed 2,147,483,647.
Awesome help as always. Why I don’t come here first every time, I have no idea.
pinal sir you r the genius hats off to your site really!
actually i had problem with default with alter you just solved it thanks sir again i say yu the best in the world for sql
Hi Sir.
How to add the default value for exsiting column
hi sir,
I need to copy the value of a primary key into another column of
another table which is a foreign key how will i do it in sql server 2008 r2
awsm
Hi sir ,
There are two table like User and comment and columns like
(Userid,Conversation,click abuse ,commentid and category) for user table and (comment id,Comments,userid).when i insert some data through asp.net website i want to update these data of two tables in database.plz what is the code for sql database
can we give reference of foreign key to primary key of table which is present in other schema table
drop table tablename
How to use triggers
HI I have a table test with primary and foreign key.and i created view with that table as vwtest.
i can insert into the vwtest as it contain single table.
how can i get primary,foreign keys into the views vwtest from the main table test
I have what appears to be a painstakingly manual task. I need to find all the foreign keys in a DB however they are all logical. There are no constraints in the DB. Can you reference a set of thechniques to facilitate this?
Thank you
What did you mean by logical? If they have foreign key constraints, it is easy to fine them
hello guys, i really need your help, i’m using sql server 2008 and in creating tables when i put foreign key the insert not accept but when there no foreign key the insert accept directly. what can i do to create that foreign key??there is another way to join tables in report without using foreign key??? please need your help i’m a beginner..thx
hey friends…tell me how to use a foreign key between 2 tables in a single database when one table have primary key..??
Hi
I am making a billling software and i can’t understand that how can i make data base for billing my structure is like this
INVOICE NO,
SLNO PRODUCTS PRICE QUANTITY TOTAL
1
2
3
4
5
6
7
8
9
10
GRANDTOTAL
Problem is that if i sell one products then it working but if i sell 10item at a time
i can,t understand how to create table for it please help me
VIVEK KUMAR
WHAT IS THE DIFFERENCE OF GIVING PRIMARY KEY AND FOREIGN KEY CONSTRAINT AT COLUMN LEVEL AND TABLE LEVEL….WE CAN PROVIDE DIRECTLY AT A TABLE LEVEL ITSELF RIGHT
helo sir,
how to insert values in a table that contains a foreign key
HI
CAN YOU DEFINE SCHEMAS AND DEFAULT CONSTRAINT