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

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.

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.

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

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

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”.