Home arrow SQL Server arrow Primary Key on Multiple Tables – New RDBMS Concept in SQL SERVER 2000
SQL SERVER

Primary Key on Multiple Tables – New RDBMS Concept in SQL SERVER 2000


The reason for this article is to demonstrate how the Multiple Table Primary Key concept can help our readers to enforce Entity Integrity across the tables embedded in an SQL Server 2000 database. Read more ...

Author Info:
By: Raghavendra Narayana
Rating: 3 stars3 stars3 stars3 stars3 stars / 32
August 10, 2003
TABLE OF CONTENTS:
  1. · Primary Key on Multiple Tables – New RDBMS Concept in SQL SERVER 2000
  2. · Conclusion

print this article
SEARCH DEVARTICLES

Primary Key on Multiple Tables – New RDBMS Concept in SQL SERVER 2000
(Page 1 of 2 )

Here is a new concept that extends the boundary of the Primary Key.  The idea is to introduce the primary key concept on more than one table.  By this we can achieve Entity Integrity across the tables.

Note: MTPK is acronym for Multiple Table Primary Key

The Concept involves a table “SysMultiPK” which is similar to other system tables that holds information about the columns, tables, and constraints.  The structure of the table is explained here.

 Column Name Datatype Length Description
 ConstraintName Varchar 50 Name of the MTPK constraint
 TableName Varchar 100 Name of table
 ColumnName Varchar 100 Name of column

An example of entry for MTPK in SysMultiPk table is shown here. 

Select * from SysMultiPK

In this example, the tables involved in MTPK for Employee and Employee_PF columns are Employee_Code and PF_Code respectively. So the composite data instance of Employee_Code and PF_Code columns should be unique.

SysMultiPK Table can be optimized to just two columns. Here it goes.

 Column Name Datatype LengthDescription
 ConstraintName Varchar 25Name of the MTPK constraint
 TableColumnSequence Varchar 1000Table and column names that are participating in MTPK are stored in ascending order delimited by the '+' sign

An example of an entry for MTPK in alternate SysMultiPk table is shown here. 

Select * from SysMultiPK

Here the example drilled down to one row.  The ‘+’ is the delimiter used between table/column names.

By making use of SysMultiPK table data i.e. defined as MTPK, Entity Integrity can be enforced across the tables using Database objects like Triggers, Stored Procedures, Function and etc.  Stored Procedures or functions can be created to check for the Entity existence.  Triggers can be created on the tables on insert/update to check the Entity uniqueness and to cancel/approve the addition/edition.

Let us create two Stored Procedures based on MTPK’s ground idea.  One with using SysMultiPK table, another without using SysMultiPK table.

Table Script to Try Out Examples

Run the following Table script to try out examples.

CREATE TABLE SysMultiPK (
 ConstraintName  varchar(50) NOT NULL,
 TableColumnName varchar(1000) NOT NULL
)
GO
CREATE TABLE [dbo].[Employee] (
 [EMPLOYEE_CODE] [char] (10) NOT NULL,
 [EMPLOYEE_NAME] [varchar] (25) NOT NULL
)
GO
CREATE TABLE [dbo].[Employee_PF] (
 [PF_Code] [char] (10) NOT NULL,
 [PF_Amount] [int] NOT NULL
)
GO
INSERT INTO SysMultiPK VALUES ('EmployeeMTPK', 'dbo.Employee.Employee_CodE + dbo.Employee_PF.PF_Code')
INSERT INTO EMPLOYEE VALUES ('EMP1', 'RAGHAVENDRA NARAYANA')
INSERT INTO EMPLOYEE VALUES ('EMP2', 'RAJEEV RAO')
INSERT INTO EMPLOYEE_PF VALUES ('PF1', 1000)
INSERT INTO EMPLOYEE_PF VALUES ('PF2', 2000)

1st Stored Procedure: MultiPK

Here the Developer needs to enter the MTPK constraint details in SysMultiPK table.  The SP requires constraint name and data to check as parameters.

Script

CREATE PROCEDURE MultiPK (@MTPK_NAME AS VARCHAR(50), @SWHERE AS VARCHAR(2000), @SDELIMITER AS VARCHAR(2000), @STATUS AS VARCHAR(200) OUTPUT)
AS
BEGIN
 DECLARE @SMTPK_NAME VARCHAR(1000)
 --Check the constraint name existance.
 SELECT @SMTPK_NAME = TableColumnSequence FROM SysMultiPK WHERE ConstraintName = @MTPK_NAME
 --Stop execution Constraint does not exist
 IF @SMTPK_NAME IS NULL OR @SMTPK_NAME = ''
 BEGIN
  SET @STATUS = 'FAILED:  CONSTRAINT NOT DEFINED IN SYSMULTIPK TABLE'
  RETURN -1
 END
 --Variable declaration
 DECLARE @SSTR VARCHAR(2000)
 DECLARE @SSTR2 VARCHAR(2000)
 DECLARE @SSQL NVARCHAR(4000)
 DECLARE @OLDPOS INTEGER
 DECLARE @NEWPOS INTEGER
 DECLARE @ICOUNT INTEGER
 DECLARE @MSSTR VARCHAR(2000)
 DECLARE @MSSTR2 VARCHAR(2000)
 DECLARE @MOLDPOS INTEGER
 DECLARE @MNEWPOS INTEGER
 DECLARE @MSDELIMITER CHAR(1)
 SET @SWHERE = LTRIM(RTRIM(@SWHERE))
 --Stop execution if Where condition is invalid
 IF LEN(@SWHERE) = 0
 BEGIN
  SET @STATUS = 'FAILED:  INVALID DATA SEARCH IN @WHERE PARAMETER'
  RETURN -1
 END
 --Set default values
 SET @STATUS = ''
 SET @OLDPOS = 1
 SET @MOLDPOS = 1
 SET @SSTR = @SWHERE
 SET @MSSTR = @SMTPK_NAME
 SET @MSDELIMITER = '+'
 --INDEFINATE LOOP
 WHILE LEN(@SSTR) > 0
 BEGIN
  --Construct SELECT SQL
  --Search for first/next Delimiter Where condition parameter
  SET @NEWPOS = CHARINDEX(@SDELIMITER, @SSTR, @OLDPOS)
  --Search for first/next Delimiter in TableColumnNames value
  SET @MNEWPOS = CHARINDEX(@MSDELIMITER, @MSSTR, @MOLDPOS)
  --Construct SQL.  Derive the Table/Column name and data to search
  IF @NEWPOS = 0
  BEGIN
   SET @SSTR2 = SUBSTRING(@SSTR, @OLDPOS, LEN(@SSTR))
   SET @MSSTR2 = SUBSTRING(@MSSTR, @MOLDPOS, LEN(@MSSTR))
  END
  ELSE
  BEGIN
   SET @SSTR2 = SUBSTRING(@SSTR, @OLDPOS, @NEWPOS-@OLDPOS)
   SET @MSSTR2 = SUBSTRING(@MSSTR, @MOLDPOS, @MNEWPOS-@MOLDPOS)
  END
  --Take Count of records to check for data existence.
  --Let the SELECT SQL result be stored in a Global Temporary table for future purpose
  SET @SSQL = N'SELECT COUNT(1) AS IICOUNT INTO ##tmpCOUNT FROM ' + LEFT(@MSSTR2, LEN(@MSSTR2) - LEN(RIGHT(@MSSTR2, CHARINDEX('.', REVERSE(@MSSTR2))))) + ' WHERE ' + @MSSTR2 + ' = ' + @SSTR2
  --Execute the Constructed SELECT SQL
  EXECUTE sp_executesql @SSQL
  --If error while executing the SQL. Break the execution
  --Send back at which point of Where condition the execution failed
  IF @@ERROR <> 0
  BEGIN
   SET @STATUS = 'FAILED:  WHILE EXECUTING CONSTRUCTED SQL STATEMENT'
   BREAK
  END
  --Get the Record Count from Temporary Table to a variable
  SELECT @ICOUNT = IICOUNT FROM ##tmpCOUNT
  --Destroy the Temporary table
  DROP TABLE ##tmpCOUNT
  --If the Record Count is 0 that means there are no matching records
  --for this particular Where condition part then stop execution
  --Send back where it failed.
  IF @ICOUNT = 0
  BEGIN
   SET @STATUS = 'FAILED: AT ' + @MSSTR2 + ' = ' + @SSTR2
   BREAK
  END
  --If the all the parts of Where condition completed then exit
  IF @NEWPOS = 0
   BREAK
  --Store the last found Delimiter position in OldPos variable for future use.
  SET @OLDPOS = @NEWPOS + LEN(@SDELIMITER)
  SET @MOLDPOS = @MNEWPOS + LEN(@MSDELIMITER)
 END
 --Check the status for errors OR value not found.
 --If any errors then return -1.  Else Return 0 with 'SUCCESSFUL' flag.
 IF LEN(@STATUS) > 0
 BEGIN
  RETURN -1
 END
 ELSE
 BEGIN
  SET @STATUS = 'SUCCESSFUL'
  RETURN 0
 END
END

Parameters

 Parameter NameDescription
 @sWhereSpecify the condition part i.e. table & column name and value to check
 @STATUS [input/output]Status of the SP execution. Output will be "Successful" or else the error description on errors

Returns

  • Returns 0 plus Status as “SUCCESSFUL” on successful executions.
  • Returns –1 plus Status as description of the Error on unsuccessful executions.

In this example, existing Data been given, should be a successful check.  The “Status” output variable should be “SUCCESSFUL”.

DECLARE @STATUS AS VARCHAR(200)
DECLARE @SDELIMITER AS VARCHAR(100)
DECLARE @SWHERE AS VARCHAR(2000)
SET @SDELIMITER = 'XYZ-XYZ-XYZ'
SET @SWHERE = '''EMP1''' + @SDELIMITER + '''PF1'''
EXEC MULTIPK 'EMPLOYEEMTPK', @SWHERE, @SDELIMITER, @STATUS = @STATUS OUTPUT
PRINT @STATUS

Here, one of the non-existing data i.e. “PF_Code = PF50” been given, should be an unsuccessful search. The “Status” output variable should contain message where the Primary Key failed.

DECLARE @STATUS AS VARCHAR(200)
DECLARE @SDELIMITER AS VARCHAR(100)
DECLARE @SWHERE AS VARCHAR(2000)
SET @SDELIMITER = 'XYZ-XYZ-XYZ'
SET @SWHERE = '''EMP1''' + @SDELIMITER + '''PF50'''
EXEC MULTIPK 'EMPLOYEEMTPK', @SWHERE, @SDELIMITER, @STATUS = @STATUS OUTPUT
PRINT @STATUS

2nd Stored Procedure – MultiPK2

Entity uniqueness can be checked without SysMultiPK table. This is an alternate approach.  This reduces creation and maintenance of SysMultiPK table. But the developer has to specify the table/column also as parameter.

Script

CREATE PROCEDURE MultiPK2 (@SWHERE AS VARCHAR(2000), @STATUS AS VARCHAR(200) OUTPUT)
AS
BEGIN
 --Variable declaration
 DECLARE @SSTR VARCHAR(2000)
 DECLARE @SSTR2 VARCHAR(2000)
 DECLARE @SSQL NVARCHAR(4000)
 DECLARE @OLDPOS INTEGER
 DECLARE @NEWPOS INTEGER
 DECLARE @ICOUNT INTEGER
 --Trimming and Default values for variables.
 SET @SWHERE = LTRIM(RTRIM(@SWHERE))
 SET @STATUS = ''
 --Remove 'Where' keyword if exist in the parameter
 IF CHARINDEX(@SWHERE, 'WHERE ') > 0
  SET @SSTR = SUBSTRING(@SWHERE, 6, LEN(@SWHERE))
 ELSE
  SET @SSTR = @SWHERE
 SET @OLDPOS = 1
 --INDEFINATE LOOP
 WHILE LEN(@SSTR) > 0
 BEGIN
  --Construct SELECT SQL
  --Search for next ' AND ' keyword
  SET @NEWPOS = CHARINDEX(' AND ', @SSTR, @OLDPOS)
  IF @NEWPOS = 0
  BEGIN
   SET @SSTR2 = SUBSTRING(@SSTR, @OLDPOS, LEN(@SSTR))
  END
  ELSE
  BEGIN
   SET @SSTR2 = SUBSTRING(@SSTR, @OLDPOS, @NEWPOS-@OLDPOS)
  END
  --Take Count of records to check for data existence.
  --Let the SELECT SQL result be stored in a Global Temporary table for future purpose
  SET @SSQL = N'SELECT COUNT(1) AS IICOUNT INTO ##tmpCOUNT FROM ' + LEFT(@SSTR2, LEN(@SSTR2) - LEN(RIGHT(@SSTR2, CHARINDEX('.', REVERSE(@SSTR2))))) + ' WHERE ' + @SSTR2
  --Execute the Constructed SELECT SQL
  EXECUTE sp_executesql @SSQL
  --If error while executing the SQL. Break the execution
  --Send back at which point of Where condition the execution failed
  IF @@ERROR <> 0
  BEGIN
   SET @STATUS = 'FAILED AT ' + @SSTR2
   BREAK
  END
  --Get the Record Count from Temporary Table to a variable
  SELECT @ICOUNT = IICOUNT FROM ##tmpCOUNT
  --Destroy the Temporary table
  DROP TABLE ##tmpCOUNT
  --If the Record Count is 0 that means there are no matching records
  --for this particular Where condition part then stop execution
  --Send back where it failed.
  IF @ICOUNT = 0
  BEGIN
   SET @STATUS = 'FAILED AT ' + @SSTR2
   BREAK
  END
  --If the all the parts of Where condition then exit
  IF @NEWPOS = 0
   BREAK
  --Store the last found ' AND ' keyword position in OldPos variable for future use.
  SET @OLDPOS = @NEWPOS + 5
 END
 --Check the status for errors OR value not found.
 --If any errors then return -1.  Else Return 0 with 'SUCCESSFUL' flag.
 IF LEN(@STATUS) > 0
  RETURN -1
 ELSE
 BEGIN
  SET @STATUS = 'SUCCESSFUL'
  RETURN 0
 END
END

Parameters

 Parameter NameDescription 
 @MTPK_NameSpecify the MTPK constraint name
 @sWhereData should be checked. Delimiter should be added for multiple values
 @sDelimiterDelimiter string used to delimit multiple values in @sWhere parameter
 @sStatus [input/output]Status of the SP execution. Output to be "Successful" or the error description on errors

Returns

  • Returns 0 plus Status as “SUCCESSFUL” on successful executions.
  • Returns –1 plus Status as description of the Error on unsuccessful executions.

In this example existing Data been given, should be a successful check.  The “Status” output variable should be “SUCCESSFUL”.

DECLARE @STATUS AS VARCHAR(200)
EXEC MULTIPK2 'EMPLOYEE.EMPLOYEE_CODE = ''EMP1'' AND EMPLOYEE_PF.PF_CODE = ''PF1''', @STATUS = @STATUS OUTPUT
PRINT @STATUS

Here, one of the non-existing data i.e. “Employee_Code = EMP20” been given, should be an unsuccessful search. The “Status” output variable should contain where the Primary Key failed.

DECLARE @STATUS AS VARCHAR(200)
EXEC MULTIPK2 'EMPLOYEE.EMPLOYEE_CODE = ''EMP20'' AND EMPLOYEE_PF.PF_CODE = ''PF1''', @STATUS = @STATUS OUTPUT
PRINT @STATUS

Usage of MTPK

To achieve Entity integrity across the tables.

To reduce number SQL statements and trips to PL/SQL engine while checking for data existence in more than one table.

You can achieve the similar thing by creating Database View on the participating Tables and columns with join conditions.  But by defining MTPK entity in SysMultiPK table gives broader usability for data handling/manipulation.

Note

You should have Database creation permission to try out the examples. 

Take care of Database objects owner names while creating and using Stored Procedure/Tables.  Here it is assumed that DB objects are created under “dbo”.


blog comments powered by Disqus
SQL SERVER ARTICLES

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables – New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials