Recently I posted a question on stackoverflow asking how, in tsql, to differentiate a 1:? from an intermediate 1:n relationship.
Well, while all the inane bullshit polls and trivial questions were getting immediate response and comments and ups, a real technical question garnered no response. hmmmm...
Anyway, as usual, I had to answer my own question.
The solution was as I always thought it was but apparently I had been going about implementing it exactly the wrong way.
Keep in mind that this is in the context of generating source code for DTO objects.
Excluding edge cases like multiple 'inheritance' and abnormal schemas, the characteristics of a 1:? relationship seem to be:
If the base side columns of the constraint fit into any one of the base side tables unique constraints AND the column count is the same then we have a 1:? and should be realized as a singular object reference.
Am I mistaken?
In any case, I took a fresh look at the problem and finally came up with a script that produces the correct results for the question as asked and for the more elaborate constraint study database I used for testing.
I would greatly appreciate any input on how to more efficiently accomplish this. A purely set based script, inline - no functions- would be sweet.
So - the test db looks like this:
DDL
CREATE TABLE [ControlParent]
(
[ControlParentId] [int] NOT NULL,
CONSTRAINT [PK_ControlParent] PRIMARY KEY CLUSTERED ( [ControlParentId] ASC )
)
CREATE TABLE [M2MInter]
(
[M2MParentAId] [int] NOT NULL,
[M2MParentBId] [int] NOT NULL,
CONSTRAINT [PK_M2MInter] PRIMARY KEY CLUSTERED ( [M2MParentAId] ASC, [M2MParentBId] ASC )
)
CREATE TABLE [M2MParentA]
(
[M2MParentAId] [int] NOT NULL,
CONSTRAINT [PK_M2MParentA] PRIMARY KEY CLUSTERED ( [M2MParentAId] ASC )
)
CREATE TABLE [M2MParentB]
(
[M2MParentBId] [int] NOT NULL,
CONSTRAINT [PK_M2MParentB] PRIMARY KEY CLUSTERED ( [M2MParentBId] ASC )
)
CREATE TABLE [M2MUQInter]
(
[M2MUQParentASKey] [int] NOT NULL,
[M2MUQParentBSKey] [int] NOT NULL,
CONSTRAINT [PK_M2MUQInter] PRIMARY KEY CLUSTERED ( [M2MUQParentASKey] ASC, [M2MUQParentBSKey] ASC )
)
CREATE TABLE [M2MUQParentA]
(
[M2MUQParentAId] [int] NOT NULL,
[M2MUQParentASKey] [int] NOT NULL,
CONSTRAINT [PK_M2MUQParentA] PRIMARY KEY CLUSTERED ( [M2MUQParentAId] ASC )
)
CREATE TABLE [M2MUQParentB]
(
[M2MUQParentBId] [int] NOT NULL,
[M2MUQParentBSKey] [int] NULL,
CONSTRAINT [PK_M2MUQParentB] PRIMARY KEY CLUSTERED ( [M2MUQParentBId] ASC )
)
CREATE TABLE [M2OChild]
(
[M2OChildId] [int] NOT NULL,
CONSTRAINT [PK_M2OChild] PRIMARY KEY CLUSTERED ( [M2OChildId] ASC )
)
CREATE TABLE [M2OParent]
(
[M2OParentId] [int] NOT NULL,
[M2OChildId] [int] NOT NULL,
CONSTRAINT [PK_M2OParent] PRIMARY KEY CLUSTERED ( [M2OParentId] ASC )
)
CREATE TABLE [O2MChild]
(
[O2MChildId] [int] NOT NULL,
[O2MParentId] [int] NOT NULL,
CONSTRAINT [PK_O2MChild_1] PRIMARY KEY CLUSTERED ( [O2MChildId] ASC )
)
CREATE TABLE [O2MParent]
(
[O2MParentId] [int] NOT NULL,
CONSTRAINT [PK_O2MParent_1] PRIMARY KEY CLUSTERED ( [O2MParentId] ASC )
)
CREATE TABLE [O2ONFKChild]
(
[O2ONFKChildId] [int] NOT NULL,
CONSTRAINT [PK_O2ONFKChild] PRIMARY KEY CLUSTERED ( [O2ONFKChildId] ASC )
)
CREATE TABLE [O2ONFKParent]
(
[O2ONFKParentId] [int] NOT NULL,
CONSTRAINT [PK_O2ONFKParent] PRIMARY KEY CLUSTERED ( [O2ONFKParentId] ASC )
)
CREATE TABLE [O2ONUQChild]
(
[O2ONUQChildId] [int] NOT NULL,
[O2ONUQParentId] [int] NOT NULL,
CONSTRAINT [PK_O2ONUQChild] PRIMARY KEY CLUSTERED ( [O2ONUQChildId] ASC ),
CONSTRAINT [IX_O2ONUQChild_O2ONUQ] UNIQUE NONCLUSTERED ( [O2ONUQParentId] ASC )
)
CREATE TABLE [O2ONUQParent]
(
[O2ONUQParentId] [int] NOT NULL,
CONSTRAINT [PK_O2ONUQParent] PRIMARY KEY CLUSTERED ( [O2ONUQParentId] ASC )
)
CREATE TABLE [O2OPKChild]
(
[O2OPKChildId] [int] NOT NULL,
CONSTRAINT [PK_O2OPKChild] PRIMARY KEY CLUSTERED ( [O2OPKChildId] ASC )
)
CREATE TABLE [O2OPKParent]
(
[O2OPKParentId] [int] NOT NULL,
CONSTRAINT [PK_O2OPKParent] PRIMARY KEY CLUSTERED ( [O2OPKParentId] ASC )
)
ALTER TABLE [M2MInter]
WITH CHECK
ADD CONSTRAINT [FK_M2MInter_M2MParentA] FOREIGN KEY ( [M2MParentAId] ) REFERENCES [M2MParentA] ( [M2MParentAId] )
ALTER TABLE [M2MInter]
CHECK CONSTRAINT [FK_M2MInter_M2MParentA]
ALTER TABLE [M2MInter]
WITH CHECK
ADD CONSTRAINT [FK_M2MInter_M2MParentB] FOREIGN KEY ( [M2MParentBId] ) REFERENCES [M2MParentB] ( [M2MParentBId] )
ALTER TABLE [M2MInter]
CHECK CONSTRAINT [FK_M2MInter_M2MParentB]
ALTER TABLE [M2MUQInter]
WITH CHECK
ADD CONSTRAINT [FK_M2MUQInter_M2MUQParentA] FOREIGN KEY ( [M2MUQParentASKey] ) REFERENCES [M2MUQParentA] ( [M2MUQParentAId] )
ALTER TABLE [M2MUQInter]
CHECK CONSTRAINT [FK_M2MUQInter_M2MUQParentA]
ALTER TABLE [M2MUQInter]
WITH CHECK
ADD CONSTRAINT [FK_M2MUQInter_M2MUQParentB] FOREIGN KEY ( [M2MUQParentBSKey] ) REFERENCES [M2MUQParentB] ( [M2MUQParentBId] )
ALTER TABLE [M2MUQInter]
CHECK CONSTRAINT [FK_M2MUQInter_M2MUQParentB]
ALTER TABLE [M2OParent]
WITH CHECK
ADD CONSTRAINT [FK_M2OParent_M2OChild] FOREIGN KEY ( [M2OChildId] ) REFERENCES [M2OChild] ( [M2OChildId] )
ALTER TABLE [M2OParent]
CHECK CONSTRAINT [FK_M2OParent_M2OChild]
ALTER TABLE [O2MChild]
WITH CHECK
ADD CONSTRAINT [FK_O2MChild_O2MParent] FOREIGN KEY ( [O2MParentId] ) REFERENCES [O2MParent] ( [O2MParentId] )
ALTER TABLE [O2MChild]
CHECK CONSTRAINT [FK_O2MChild_O2MParent]
ALTER TABLE [O2ONFKChild]
WITH CHECK
ADD CONSTRAINT [FK_O2ONFKChild_O2ONFKParent] FOREIGN KEY ( [O2ONFKChildId] ) REFERENCES [O2ONFKParent] ( [O2ONFKParentId] )
ALTER TABLE [O2ONFKChild]
CHECK CONSTRAINT [FK_O2ONFKChild_O2ONFKParent]
ALTER TABLE [O2ONUQChild]
WITH CHECK
ADD CONSTRAINT [FK_O2ONUQChild_O2ONUQParent] FOREIGN KEY ( [O2ONUQParentId] ) REFERENCES [O2ONUQParent] ( [O2ONUQParentId] )
ALTER TABLE [O2ONUQChild]
CHECK CONSTRAINT [FK_O2ONUQChild_O2ONUQParent]
ALTER TABLE [O2OPKParent]
WITH CHECK
ADD CONSTRAINT [FK_O2OPKParent_O2OPKChild] FOREIGN KEY ( [O2OPKParentId] ) REFERENCES [O2OPKChild] ( [O2OPKChildId] )
ALTER TABLE [O2OPKParent]
CHECK CONSTRAINT [FK_O2OPKParent_O2OPKChild]
And the script that produces expected results of identifying 1:? relationships:
/*
Will identify immediate 1:? fk relationships
*/
-- TODO: puzzle: work out the set-based equivalent
SET NOCOUNT ON
BEGIN -- Get a table full of PK and UQ columns
DECLARE @unique_keys TABLE
(
-- contains PK and UQ indexes
[schema_name] NVARCHAR(128),
table_name NVARCHAR(128),
index_name NVARCHAR(128),
column_id INT,
column_name NVARCHAR(128),
is_primary_key BIT,
is_unique_constraint BIT,
is_unique BIT
)
INSERT INTO @unique_keys
(
[schema_name],
table_name,
index_name,
column_id,
column_name,
is_primary_key,
is_unique_constraint,
is_unique
)
-- selects PK and UQ indexes
SELECT S.name AS [schema_name],
T.name AS table_name,
IX.name AS index_name,
IC.column_id,
C.name AS column_name,
IX.is_primary_key,
IX.is_unique_constraint,
IX.is_unique
FROM sys.tables AS T
INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
INNER JOIN sys.indexes AS IX ON T.object_id = IX.object_id
INNER JOIN sys.index_columns AS IC ON IX.object_id = IC.object_id
AND IX.index_id = IC.index_id
INNER JOIN sys.columns AS C ON IC.column_id = C.column_id
AND IC.object_id = C.object_id
WHERE ( IX.is_unique = 1 )
AND ( T.name <> 'sysdiagrams' )
AND IX.is_unique = 1
ORDER BY schema_name,
table_name,
index_name,
C.column_id
END
BEGIN -- Get a table full of FK columns
DECLARE @foreign_key_columns TABLE
(
constraint_name NVARCHAR(128),
base_schema_name NVARCHAR(128),
base_table_name NVARCHAR(128),
base_column_id INT,
base_column_name NVARCHAR(128),
unique_schema_name NVARCHAR(128),
unique_table_name NVARCHAR(128),
unique_column_id INT,
unique_column_name NVARCHAR(128)
)
INSERT INTO @foreign_key_columns
(
constraint_name,
base_schema_name,
base_table_name,
base_column_id,
base_column_name,
unique_schema_name,
unique_table_name,
unique_column_id,
unique_column_name
)
SELECT FK.name AS constraint_name,
S.name AS base_schema_name,
T.name AS base_table_name,
C.column_id AS base_column_id,
C.name AS base_column_name,
US.name AS unique_schema_name,
UT.name AS unique_table_name,
UC.column_id AS unique_column_id,
UC.name AS unique_column_name
FROM sys.tables AS T
INNER JOIN sys.schemas AS S ON T.schema_id = S.schema_id
INNER JOIN sys.foreign_keys AS FK ON T.object_id = FK.parent_object_id
INNER JOIN sys.foreign_key_columns AS FKC ON FK.object_id = FKC.constraint_object_id
INNER JOIN sys.columns AS C ON FKC.parent_object_id = C.object_id
AND FKC.parent_column_id = C.column_id
INNER JOIN sys.columns AS UC ON FKC.referenced_object_id = UC.object_id
AND FKC.referenced_column_id = UC.column_id
INNER JOIN sys.tables AS UT ON FKC.referenced_object_id = UT.object_id
INNER JOIN sys.schemas AS US ON UT.schema_id = US.schema_id
WHERE ( T.name <> 'sysdiagrams' )
ORDER BY base_schema_name,
base_table_name
END
DECLARE @constraint_name NVARCHAR(128),
@base_schema_name NVARCHAR(128),
@base_table_name NVARCHAR(128),
@unique_schema_name NVARCHAR(128),
@unique_table_name NVARCHAR(128)
-- The foreign key side of the constraint is always singular, we need to check from the perspective
-- of the unique side of the constraint.
-- for each FK constraint in DB
DECLARE tmpC CURSOR READ_ONLY
FOR SELECT DISTINCT
constraint_name,
base_schema_name,
base_table_name,
unique_schema_name,
unique_table_name
FROM @foreign_key_columns
OPEN tmpC
FETCH NEXT FROM tmpC INTO @constraint_name, @base_schema_name, @base_table_name, @unique_schema_name, @unique_table_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- get the columns in the base side of the FK constraint
DECLARE @fkc TABLE
(
column_name NVARCHAR(128)
)
DELETE FROM @fkc
INSERT INTO @fkc ( column_name )
SELECT base_column_name
FROM @foreign_key_columns
WHERE constraint_name = @constraint_name
-- check for one to one/none
-- If the base side columns of the constraint fit into any one of the base side tables unique constraints
-- AND the column count is the same then we have a one-to-one/none and should be realized as a singular
-- object reference
-- I realize that if the base side unique constraint has more columns than the unique side unique constraint
-- AND all of those columns DO represent a 1:? that would actually qualify but it seems like an edge case and
-- beyond the scope of this question.
DECLARE @uk_schema_name NVARCHAR(128),
@uk_table_name NVARCHAR(128),
@uk_index_name NVARCHAR(128),
@is_may_have_a BIT
SET @is_may_have_a = 0
-- have to open another cursor over the unique keys of the base table - i want
-- a distinct list of unique constraints for the base table
DECLARE cKey CURSOR READ_ONLY
FOR SELECT DISTINCT
[schema_name],
table_name,
index_name
FROM @unique_keys
WHERE [schema_name] = @base_schema_name
AND table_name = @base_table_name
OPEN cKey
FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
WHILE @@FETCH_STATUS = 0
BEGIN
-- get the unique constraint columns
DECLARE @pkc TABLE
(
column_name NVARCHAR(128)
)
DELETE FROM @pkc
INSERT INTO @pkc ( column_name )
SELECT column_name
FROM @unique_keys
WHERE [schema_name] = @uk_schema_name
AND table_name = @uk_table_name
AND index_name = @uk_index_name
-- if count is same and columns are same
DECLARE @count1 INT, @count2 INT
SELECT @count1 = COUNT(*) FROM @fkc
SELECT @count2 = COUNT(*) FROM @pkc
IF @count1 = @count2
BEGIN
-- select all from both on name and exclude mismatches
SELECT @count1 = COUNT(*)
FROM @fkc F
FULL OUTER JOIN @pkc P ON f.column_name = p.column_name
WHERE NOT p.column_name IS NULL AND NOT f.column_name IS NULL
IF @count1 = @count2
BEGIN
-- the base side of the fk constraint corresponds exactly to
-- at least on unique constraint making it effectively 1:?
SET @is_may_have_a = 1
BREAK
END
END
FETCH NEXT FROM cKey INTO @uk_schema_name, @uk_table_name, @uk_index_name
END
CLOSE cKey
DEALLOCATE cKey
IF @is_may_have_a = 1
PRINT 'for ' + @unique_schema_name + '.' + @unique_table_name + ' constraint ' + + @constraint_name + ' is 1:? '
FETCH NEXT FROM tmpC INTO @constraint_name, @base_schema_name, @base_table_name, @unique_schema_name, @unique_table_name
END
CLOSE tmpC
DEALLOCATE tmpC
the (correct) results....
for dbo.O2ONFKChild constraint FK_O2ONFKChild_O2ONFKParent is 1:?
for dbo.O2ONUQChild constraint FK_O2ONUQChild_O2ONUQParent is 1:?
for dbo.O2OPKParent constraint FK_O2OPKParent_O2OPKChild is 1:?
Am I missing a simpler way to do this? Is my sql ugly? Well say so and point me the right direction.....
EDIT: fixed small typo in FK query that returned wrong unique table.
Technorati tags:
SQL