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
Can’t we create a default constraint at table level ???
Hi Mr. Pinal,
One table having one cluster Index in the case of one primary key. If more than one primary keys are there in a table, then how many clustered Indexes are created for table?
Please reply me……
Thanks & Regards
Madhu….
Note that there cannot be more than one primary key in a table
Hi madhivanan,
we can create more than one primary key we can call it as composite key..
It is a single primary key combined on many columns. You cannot create primary key on each column seperately.
Hi, greeting from Indonesia. I’ve been looking for script of creating a foreign key related to the primary key from the column level and your explanation was great. Thank you very much! God bless
Greetings Pinal – Always find your answers helpful and spot on. At the moment I’m stuck in SQL/Server hell and can’t find a solution. I have a table that I can’t drop, can’t truncate, can’t do anything with because of this message “it is being used for foreign key constraint enforcement.” I have dropped ALL of the constraints on the table, and still can’t get around this error. There is a unique index that is being referenced that can’t be dropped either because of the same error response. I’m stuck in a circular hell. How do I get out of hell with this table?
Your posts are really usefull
Thanks for your post.
Thank you, always seem to forget the exact syntax on many of those.
how to insert a manual int value in primary key , sql server 2008
You can do it in INSERT and UPDATE statements
Hi sir,
My question is-
In sql server authentication mode which is more secure authentication? (Windows or Sql server)
window authentication mode used when you use your application with your system and sql server authentication is used when multiple application used a centrally database .
How do I fetch MAC address in SQL server to implement in triggers.
Dave, thanks for you feedback, because this examples are very good and help me so much.
Thanks,
JCSL.
Great sir, thanks…
How to generate a default Primary Key
Background: There is a “Column Properties–Identity Specification” option with “Identy Increment” and “Identity Seed” in sqlsrv.
Q: Is it possible to assign a default value for new records like “getYear() + get_SQLSRV_Global_Sequence_Number()” feature/trigger available?
1) Example:
a) Syntax
(SELECT CONVERT( NUMERIC(4), (SELECT CONVERT(VARCHAR(4), GETDATE(), 112) AS [YYYY]))) + <SOME_GLOBAL_UNIQUE_SEED_FUNCTION()
2) SQL example
SELECT (SELECT CONVERT( VARCHAR(4), (SELECT CONVERT(VARCHAR(4), GETDATE(), 112) AS [YYYY]))) PK_PART1_YEAR, 1000000 as PK_PART2_SQLSRV_SEQUENCE
3) Expected result
Assigned primary key value: YEAR+CONCAT
1) First value this year: 20121000001
2) Second value this year: 20121000002
3) N-value for year 2050: 20501000002
Please refer or provide an sqlsrv example if possible…we would like to have a standard approach(dba’s, C# developers) regarding primary key assignment…
-kurt
Forgot to mention that
a) external systems (PRODUCERS) post typically + + in a Varchar(50) column
…and that
b) i’m using an additional “uniqueidentifier” and “(newid())” in sqlsrv db for internal integrity.
Hello sir,
How to create recursive query in SQL Server?
ur post was very nice and useful for me
Hello sir?
now i save the data in one table , that values also create a new columns into another table , if any possible to do automatically create a new columns
sir,
Am Jyothi. Is it posible to create a foreign with out primary and it is a unique key.
Hello Sir,
Can we drop foreign key which is not a constraint.. How to drop foreign key …
Use ALTER TABLE and DROP CONSTRAINT statements
Thanks for the reply.. I tried with this, its giving error field is not constraint..
How to drop that foreign key now…
You should find the key name and use it
Drop constraint contrain_name
How to find the constraint name..