Feeds:
Posts
Comments

Archive for the ‘SQL Constraint and Keys’ Category

SQL SERVER – Disable CHECK Constraint – Enable CHECK Constraint

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Index, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on November 12, 2009 | 4 Comments »

Foreign Key and Check Constraints are two types of constraints that can be disabled or enabled when required. This type of operation is needed when bulk loading operations are required or when there is no need to validate the constraint. The T-SQL Script that does the same is very simple.
USE AdventureWorks
GO
– Disable the constraint
ALTER TABLE HumanResources.Employee
NOCHECK CONSTRAINT CK_Employee_BirthDate
GO
– Enable the constraint
ALTER TABLE HumanResources.Employee
WITH CHECK CHECK CONSTRAINT CK_Employee_BirthDate
GO
It [...]

Read Full Post »

SQL SERVER – Difference Between Candidate Keys and Primary Key

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on October 22, 2009 | 14 Comments »

Introduction
Not long ago, I had an interesting and extended debate with one of my friends regarding which column should be primary key in a table. The debate instigated an in-depth discussion about candidate keys and primary keys. My present article revolves around the two types of keys.
Let us first try to grasp the definition of [...]

Read Full Post »

SQL SERVER – Discussion – Effect of Missing Identity on System – Real World Scenario

Posted in DBA, Database, Pinal Dave, Readers Contribution, Readers Question, SQL, SQL Authority, SQL Constraint and Keys, SQL Index, SQL Puzzle, SQL Query, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology on August 11, 2009 | 6 Comments »

About a week ago, SQL Server Expert, Imran Mohammed, provided a script, which will list all the missing identity values of a table in a database. In this post, I asked my readers if any could write a similar or better script. The results were interesting. While no one provided a new script, my question [...]

Read Full Post »

SQL SERVER – Puzzle – Write Script to Generate Primary Key and Foreign Key

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Puzzle, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology, tagged on July 23, 2009 | 30 Comments »

In one of my recent projects, a large database migration project, I confronted a peculiar situation. SQL Server tables were already moved from Database_Old to Database_New. However, all the Primary Key and Foreign Keys were yet to be moved from the old server to the new server.
Please note that this puzzle is to be solved [...]

Read Full Post »

SQL SERVER – Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, SQLServer, T SQL, Technology on July 17, 2009 | 7 Comments »

There are two different methods of retrieving the list of Primary Keys and Foreign Keys from database.
Method 1: INFORMATION_SCHEMA
SELECT
DISTINCT
Constraint_Name AS [Constraint],
Table_Schema AS [Schema],
Table_Name AS [TableName]
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE
GO

Method 2: sys.objects
SELECT OBJECT_NAME(OBJECT_ID) AS NameofConstraint,
SCHEMA_NAME(schema_id) AS SchemaName,
OBJECT_NAME(parent_object_id) AS TableName,
type_desc AS ConstraintType
FROM sys.objects
WHERE type_desc IN (‘FOREIGN_KEY_CONSTRAINT’,'PRIMARY_KEY_CONSTRAINT’)
GO

I am often asked about my preferred method of retrieving list of Primary [...]

Read Full Post »

SQL SERVER – Clustered Index on Separate Drive From Table Location

Posted in Best Practices, Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Index, SQL Optimization, SQL Performance, SQL Query, SQL Server, SQL Tips and Tricks, T SQL, Technology on June 18, 2009 | 7 Comments »

How to improve performance of SQL Server Queries is a common topic of discussion among many of us. Much has been said, much has been discussed. Few days back, I had an interesting discussion with one of the Junior developers regarding performance improvement of SQL Server Queries. We discussed on how by using a separate [...]

Read Full Post »

SQL SERVER – Difference Between Candidate Keys and Primary Key

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Function, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 30, 2009 | Leave a Comment »

Let us first try to grasp the definition of the two keys.
Candidate Key – A Candidate Key can be any column or a combination of columns that can qualify as unique key in database. There can be multiple Candidate Keys in one table. Each Candidate Key can qualify as Primary Key.
Primary Key – [...]

Read Full Post »

SQL SERVER – How to Drop Primary Key Contraint

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on May 12, 2009 | 13 Comments »

One area that always, unfailingly pulls my interest is SQL Server Errors and their solution. I enjoy the challenging task of passing through the maze of error to find a way out with a perfect solution. However, when I received the following error from one of my regular readers, I was a little stumped at [...]

Read Full Post »

SQL SERVER – 2008 – Find Relationship of Foreign Key and Primary Key using T-SQL – Find Tables With Foreign Key Constraint in Database

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on February 26, 2009 | 2 Comments »

While searching for how to find Primary Key and Foreign Key relationship using T-SQL, I came across my own blog article written earlier SQL SERVER – 2005 – Find Tables With Foreign Key Constraint in Database. It is really handy script and not found written on line anywhere. This is one really unique script and [...]

Read Full Post »

SQL SERVER – Observation – Effect of Clustered Index over Nonclustered Index

Posted in Pinal Dave, SQL, SQL Authority, SQL Constraint and Keys, SQL Index, SQL Optimization, SQL Performance, SQL Query, SQL Scripts, SQL Server, SQL Tips and Tricks, T SQL, Technology on February 21, 2009 | 9 Comments »

Note: This article is re-write of my previous article SQL SERVER – Observation – Effect of Clustered Index over Nonclustered Index. I have received so many request that re-write it as it is little confusing. I am going to re-write this with simpler words.
Query optimization is one art which is difficult to master. Just like [...]

Read Full Post »

Older Posts »