Arquivo

Archive for the ‘Keys and Constraints’ Category

Identity column – Reset

2011/08/14 Deixe um comentário
Reset Identity Column in SQL
August 11, 2011 at 2:17 AM
I’ll be honest, I am partially writing this because I always forget how to reset the identity column on a table and I want to be able to just pull up my blog and look it up.  Yet again today I had to do this and I couldn’t remember the exact syntax, so here it is.  The next record is going to be the seed value plus 1, or in the case of this example I will use zero so the first record is 1.  That will make my O.C.D. happy to see my table starting with a key value of 1!

DBCC CHECKIDENT('<TableName>', RESEED, <StartingValue - 1>)

DBCC CHECKIDENT('DimGeography', RESEED, 0)
Categorias:Keys and Constraints

Find Violating SQL Server Foreign Key Values

2011/07/22 Deixe um comentário

http://www.mssqltips.com/tip.asp?tip=2326

In many databases, foreign key constraints are not used initially and then later created on tables after data already exists.  If data already exists and there are possible foreign key violations the constraint may be created using the NOCHECK option.  In addition, sometimes when loading BULK data, foreign keys are disabled or removed to make the load faster.  Because of these issues you may have orphan data in your database tables.  There are ways to check this using NOT IN or NOT EXISTS queries, but in this tip we look at another method and a stored procedure built around this method to find records in violation.

Solution

The solution I’m suggesting is to create a stored procedure called usp_Find_Non_Integrity_FK_Vals.  This procedure takes parameters for the parent and child table names along with the parent and child schema names and then determines if there are any issues.  The procedure looks up the foreign key constraint name by querying the INFORMATION_SCHEMA views and then executes DBCC CHECKCONSTRAINTS.  The DBCC CHECKCONSTRAINTS statement is a special statement that checks the integrity of a specified constraint or all constraints on a specified table in the current database.  In addition, the procedure formats the output a little different versus the standard output from DBCC CHECKCONSTRAINTS.

Here is the stored procedure.

CREATE PROCEDURE dbo.usp_Find_Non_Integrity_FK_Vals
(@ParentSchemaName SYSNAME = 'dbo',
@ParentTableName SYSNAME,
@ChildSchemaName SYSNAME = 'dbo',
@ChildTableName SYSNAME)
AS
BEGIN
DECLARE @tsql VARCHAR(300)
DECLARE @FKconstrName VARCHAR(50)SET NOCOUNT ON

— get the foreign key constraint name from parent and child table names
SELECT @FKconstrName = a.CONSTRAINT_NAME
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS a,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE b1,
INFORMATION_SCHEMA.KEY_COLUMN_USAGE b2
WHERE a.UNIQUE_CONSTRAINT_NAME = b1.CONSTRAINT_NAME AND
a.UNIQUE_CONSTRAINT_CATALOG = b1.CONSTRAINT_CATALOG AND
a.UNIQUE_CONSTRAINT_SCHEMA = b1.CONSTRAINT_SCHEMA AND
a.CONSTRAINT_NAME = b2.CONSTRAINT_NAME AND
a.CONSTRAINT_CATALOG = b2.CONSTRAINT_CATALOG AND
a.CONSTRAINT_SCHEMA = b2.CONSTRAINT_SCHEMA AND
b1.TABLE_NAME = @ParentTableName AND
b2.TABLE_NAME = @ChildTableName AND
b1.CONSTRAINT_SCHEMA = @ParentSchemaName AND
b2.CONSTRAINT_SCHEMA = @ChildSchemaName

— construct a DBCC CHECKCONSTRAINTS TSQL
SET @tsql = ‘DBCC CHECKCONSTRAINTS (‘ + ”” + @FKconstrName + ”” + ‘)’

CREATE TABLE ##tmp (TName SYSNAME,
constName SYSNAME,
whrClause VARCHAR(1000))

— EXEC TSQL Dynamically and get all integrity FK problems
INSERT ##tmp EXEC (@tsql)

— output the results
SELECT SUBSTRING (whrClause,1,
CHARINDEX (‘=’,whrClause,1) – 1) AS FKcol,
REPLACE (SUBSTRING (whrClause,
CHARINDEX (‘=’,whrClause,1) +1,
LEN (whrClause) – CHARINDEX (‘=’,whrClause,1) – 1 ),””,”) AS FKval
FROM ##tmp

— drop temporary table
DROP TABLE ##tmp

END
GO

To test this, I have created a simple example to illustrate this issue.

Let’s say we have these two tables.

  • DBPlatform – a table consisting of different RDMBS platforms such as MSSQL and ORACLE
  • Applications – a table consisting of various applications that run on these RDBMS platforms

After the tables are created we add some data to both tables.

After the data was inserted we create a foreign key (FK) between the two tables.  To make this example work we used the NOCHECK option when we created the FK.  This will allow us to create the foreign key even if there are foreign key violations.

Here is the T-SQL example script.

CREATE TABLE DBPlatform (Fid INT IDENTITY NOT NULL, Fdesc CHAR(8))
GOALTER TABLE DBPlatform ADD CONSTRAINT PK_DBPlatform PRIMARY KEY (Fid)
GO

INSERT INTO DBPlatform VALUES (‘MSSQL’)
INSERT INTO DBPlatform VALUES (‘ORACLE’)
GO

CREATE TABLE Applications ([Sid] INT IDENTITY NOT NULL, Fid INT,Sdesc CHAR(20))
GO

ALTER TABLE Applications ADD CONSTRAINT PK_Application PRIMARY KEY ([Sid] )
GO

INSERT INTO Applications VALUES (1,’SALES’)
INSERT INTO Applications VALUES (3,’ACCOUNTS’)
INSERT INTO Applications VALUES (2,’BILLING’)
INSERT INTO Applications VALUES (2,’DWH’)
GO

ALTER TABLE Applications WITH NOCHECK
ADD CONSTRAINT FK_Applications_DBPlatform FOREIGN KEY (Fid) REFERENCES DBPlatform (Fid)
GO

/* Now, In order to show the violating rows, execute the following */
EXEC usp_Find_Non_Integrity_FK_Vals @ParentTableName = ‘DBPlatform’, @ChildTableName = ‘Applications’
GO

SELECT * FROM Applications WHERE Fid = 3
GO

Below is the output after running the stored procedure to find any foreign key violations.

The first result set shows the foreign key column and the value that is in violation.

The second result set shows the detail line of our record in violation.

That’s all there is to it.  A simple way to find foreign key violations.  Please note that the procedure was tested on both SQL Server 2008 and 2005.

Next Steps

Categorias:Keys and Constraints