Primary Key on Multiple Tables – New RDBMS Concept in SQL SERVER 2000
(Page 1 of 2 )
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 ...
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 | Length | Description |
| ConstraintName | Varchar | 25 | Name of the MTPK constraint |
| TableColumnSequence | Varchar | 1000 | Table 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 Name | Description |
| @sWhere | Specify 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 Name | Description |
| @MTPK_Name | Specify the MTPK constraint name |
| @sWhere | Data should be checked. Delimiter should be added for multiple values |
| @sDelimiter | Delimiter 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”.
Next: Conclusion >>
More SQL Server Articles
More By Raghavendra Narayana
