Background
We have these tables with constrained relationship.
-- DROP TABLE FKeyTable3 -- DROP TABLE FKeyTable3 -- DROP TABLE FKeyTable1 -- DROP TABLE KeyTable CREATE TABLE KeyTable ( KeyColumn nvarchar(2) NOT NULL ) ALTER TABLE KeyTable ADD CONSTRAINT PK_KeyTable PRIMARY KEY (KeyColumn) CREATE TABLE FKeyTable1 ( KeyColumn nvarchar(2) NOT NULL, ValueColumn nvarchar(13) ) ALTER TABLE FKeyTable1 ADD CONSTRAINT FK_FKeyTable1 FOREIGN KEY (KeyColumn) REFERENCES KeyTable(KeyColumn) CREATE TABLE FKeyTable2 ( KeyColumn nvarchar(2) NOT NULL, ValueColumn nvarchar(13) ) ALTER TABLE FKeyTable2 ADD CONSTRAINT FK_FKeyTable2 FOREIGN KEY (KeyColumn) REFERENCES KeyTable(KeyColumn) CREATE TABLE FKeyTable3 ( KeyColumn nvarchar(2) NOT NULL, ValueColumn nvarchar(13) ) ALTER TABLE FKeyTable3 ADD CONSTRAINT FK_FKeyTable3 FOREIGN KEY (KeyColumn) REFERENCES KeyTable(KeyColumn)
| KeyColumn |
|---|
| BL |
| BM |
| CA |
| CI |
| CL |
| CS |
| CV |
Issue
I would like to add a requirement to these tables. The KeyColumn in KeyTable should list either zero or one time in its foreign key table. e.g. if FKeyTable1 has ‘CI’ KeyColumn value, the ‘CI’ cannot be found in table FKeyTable2 and in table FKeyTable3.
Question
Write a SQL that list all the KeyColumn value violating the one of zero time entry rule.
Answer
SELECT COUNT(KeyColumn) as KeyCount, KeyColumn
FROM (SELECT KeyColumn FROM FKeyTable1
UNION ALL SELECT KeyColumn FROM FKeyTable2
UNION ALL SELECT KeyColumn FROM FKeyTable3) TestKey
GROUP BY KeyColumn
HAVING COUNT(KeyColumn) > 1
In essence, I need to create a temporary table to get KeyColumn count in all foreign key tables, using GROUP BY and HAVING to help counting and filtering.
Note
This original issue was even more complicated. I have to create a KeyColumn based on another subquery. To clarify the issue and to simplify the questioning, I rewrite this issue just to bare essential.
