Vraag TSQL externe sleutels op views?


Ik heb een SQL-Server 2008-database en een schema dat beperkingen voor externe sleutels gebruikt om referentiële integriteit af te dwingen. Werkt zoals bedoeld. Nu maakt de gebruiker weergaven van de originele tabellen om alleen aan deelverzamelingen van de gegevens te werken. Mijn probleem is dat het filteren van bepaalde datasets in sommige tabellen, maar niet in andere, de beperkingen van de externe sleutel zal schenden.
Stel je twee tabellen voor "één" en "twee". "one" bevat slechts een id kolom met waarden 1,2,3. "Twee" referenties "één". Nu maakt u weergaven voor beide tabellen. De weergave voor tabel "two" filtert niets, terwijl de weergave voor tabel "one" alle rijen verwijdert, maar de eerste. Je zult eindigen met inzendingen in de tweede weergave die nergens naar verwijzen.

Is er een manier om dit te voorkomen? Kunt u buitenlandse toetsaanspraken hebben tussen weergaven?

Enige verduidelijking in reactie op enkele van de opmerkingen:
Ik ben me ervan bewust dat de onderliggende beperkingen de integriteit van de gegevens zullen waarborgen, zelfs bij het invoegen van de views. Mijn probleem ligt bij de uitspraken die de opvattingen verbruiken. Die uitspraken zijn geschreven met de originele tabellen in het achterhoofd en gaan ervan uit dat bepaalde joins niet kunnen falen. Deze aanname is altijd geldig bij het werken met de tabellen - maar mogelijk worden doorzichten doorbroken.
Het samenvoegen / controleren van alle beperkingen bij het maken van de views op de eerste plaats is annyoing vanwege het grote aantal referentietabellen. Dus ik hoopte dat te vermijden.


20
2017-12-18 14:00


oorsprong


antwoorden:


Peter is hier al mee bezig, maar de beste oplossing is om:

  1. Maak de "hoofd" -logica (die de tabel met verwijzingen filtert) één keer.
  2. Zorg ervoor dat alle weergaven van gerelateerde tabellen tot de uitzicht gemaakt voor (1), niet de originele tabel.

D.w.z.,

CREATE VIEW v1 AS SELECT * FROM table1 WHERE blah

CREATE VIEW v2 AS SELECT * FROM table2 WHERE EXISTS
  (SELECT NULL FROM v1 WHERE v1.id = table2.FKtoTable1)

Zeker, syntactische suiker voor het verspreiden van filters voor weergaven van een tabel naar weergaven van ondergeschikte tabellen zou handig zijn, maar helaas, het maakt geen deel uit van de SQL-standaard. Dat gezegd hebbende, deze oplossing is nog steeds goed genoeg - efficiënt, rechtlijnig, onderhoudbaar en garandeert de gewenste status voor de consumerende code.


7
2018-01-02 04:16



Ik hou van je vraag. Het schreeuwt van bekendheid met de Query Optimizer, en hoe het kan zien dat sommige joins overbodig zijn als ze geen doel dienen, of als het iets kan vereenvoudigen wetende dat er maximaal één hit aan de andere kant van een join is.

De grote vraag is dus of je een FK kunt maken tegen de CIX van een geïndexeerde weergave. En het antwoord is nee.

create table dbo.testtable (id int identity(1,1) primary key, val int not null);
go
create view dbo.testview with schemabinding as
select id, val
from dbo.testtable
where val >= 50
;
go
insert dbo.testtable
select 20 union all
select 30 union all
select 40 union all
select 50 union all
select 60 union all
select 70 
go
create unique clustered index ixV on dbo.testview(id);
go
create table dbo.secondtable (id int references dbo.testview(id));
go

Dit alles werkt behalve de laatste verklaring, welke fouten met:

Msg 1768, Level 16, State 0, Line 1
Foreign key 'FK__secondtable__id__6A325CF7' references object 'dbo.testview' which is not a user table.

Dus de sleutel Foreign moet verwijzen naar een gebruikerstabel.

Maar ... de volgende vraag gaat over of je zou kunnen verwijzen naar een unieke index die is gefilterd in SQL 2008, om een ​​beeldachtige FK te krijgen.

En toch is het antwoord nee.

create unique index ixUV on dbo.testtable(val) where val >= 50;
go

Dit is gelukt.

Maar nu als ik probeer een tabel te maken die verwijst naar de val kolom

create table dbo.thirdtable (id int identity(1,1) primary key, val int not null check (val >= 50) references dbo.testtable(val));

(Ik hoopte dat de controlebeperking die overeenkomt met het filter in de gefilterde index, het systeem kan helpen begrijpen dat de FK moet werken)

Maar ik krijg een foutmelding:

There are no primary or candidate keys in the referenced table 'dbo.testtable' that matching the referencing column list in the foreign key 'FK__thirdtable__val__0EA330E9'.

Als ik de gefilterde index laat vallen en een niet-gefilterde unieke niet-geclusterde index maak, dan kan ik zonder problemen dbo.thirdtable maken.

Dus ik ben bang dat het antwoord nog steeds nee lijkt te zijn


14
2018-01-04 06:06



Het kostte me wat tijd om het misverstand hier te achterhalen - niet zeker of ik het nog steeds volledig begrijp, maar hier is het. Ik zal een voorbeeld gebruiken, dichtbij de jouwe, maar met wat gegevens - makkelijker voor mij om in deze termen te denken.

Dus de eerste twee tafels; A = Afdeling B = Werknemer

CREATE TABLE Department
  ( 
   DepartmentID int PRIMARY KEY
  ,DepartmentName varchar(20)
  ,DepartmentColor varchar(10)
  )
GO 
CREATE TABLE Employee
  ( 
   EmployeeID int PRIMARY KEY
  ,EmployeeName varchar(20)
  ,DepartmentID int FOREIGN KEY REFERENCES Department ( DepartmentID )
  )
GO 

Nu gooi ik wat gegevens in

INSERT  INTO Department
  ( DepartmentID, DepartmentName, DepartmentColor )
 SELECT 1, 'Accounting', 'RED' UNION
 SELECT 2, 'Engineering', 'BLUE' UNION
 SELECT 3, 'Sales', 'YELLOW'  UNION
 SELECT 4, 'Marketing', 'GREEN' ;

INSERT  INTO Employee
  ( EmployeeID, EmployeeName, DepartmentID )
 SELECT 1, 'Lyne', 1 UNION
 SELECT 2, 'Damir', 2 UNION
 SELECT 3, 'Sandy', 2 UNION
 SELECT 4, 'Steve', 3 UNION
 SELECT 5, 'Brian', 3 UNION
 SELECT 6, 'Susan', 3 UNION
    SELECT 7, 'Joe', 4 ;

Dus nu maak ik een weergave op de eerste tabel om sommige afdelingen te filteren.

CREATE VIEW dbo.BlueDepartments
AS
SELECT * FROM dbo.Department
WHERE DepartmentColor = 'BLUE'
GO

Dit komt terug

DepartmentID DepartmentName       DepartmentColor
------------ -------------------- ---------------
2            Engineering          BLUE

En volgens uw voorbeeld voeg ik een weergave toe voor de tweede tabel die niets filtert.

CREATE VIEW dbo.AllEmployees
AS
SELECT * FROM dbo.Employee
GO

Dit komt terug

EmployeeID  EmployeeName         DepartmentID
----------- -------------------- ------------
1           Lyne                 1
2           Damir                2
3           Sandy                2
4           Steve                3
5           Brian                3
6           Susan                3
7           Joe                  4

Het lijkt mij dat u denkt dat werknemer nr. 5, departement-id = 3 punten nergens naar verwijst?

"Je zult eindigen met inzendingen in de   tweede blik die nergens naar verwijst. "

Wel, het wijst naar de Department tafel DepartmentID = 3, zoals opgegeven met de externe sleutel. Zelfs als je het probeert join view on view niets is gebroken:

SELECT  e.EmployeeID
       ,e.EmployeeName
       ,d.DepartmentID
       ,d.DepartmentName
       ,d.DepartmentColor
FROM    dbo.AllEmployees AS e
        JOIN dbo.BlueDepartments AS d ON d.DepartmentID = e.DepartmentID
        ORDER BY e.EmployeeID

Komt terug

EmployeeID  EmployeeName         DepartmentID DepartmentName       DepartmentColor
----------- -------------------- ------------ -------------------- ---------------
2           Damir                2            Engineering          BLUE
3           Sandy                2            Engineering          BLUE   

Er is hier dus niets gebroken, de join heeft simpelweg geen overeenkomende records gevonden DepartmentID <> 2 Dit is eigenlijk hetzelfde als ik toetreden tot tafels en dan inclusief filter zoals in de eerste weergave:

SELECT  e.EmployeeID
       ,e.EmployeeName
       ,d.DepartmentID
       ,d.DepartmentName
       ,d.DepartmentColor
FROM    dbo.Employee AS e
        JOIN dbo.Department AS d ON d.DepartmentID = e.DepartmentID
        WHERE d.DepartmentColor = 'BLUE'
     ORDER BY e.EmployeeID

Retourneert opnieuw:

EmployeeID  EmployeeName         DepartmentID DepartmentName       DepartmentColor
----------- -------------------- ------------ -------------------- ---------------
2           Damir                2            Engineering          BLUE
3           Sandy                2            Engineering          BLUE

In beide gevallen mislukken joins niet, ze doen gewoon wat ze verwachten.

Nu zal ik proberen de referentiële integriteit te doorbreken door een weergave (er is geen DepartmentID = 127)

INSERT  INTO dbo.AllEmployees
      ( EmployeeID, EmployeeName, DepartmentID )
VALUES( 10, 'Bob', 127 )

En dit resulteert in:

Msg 547, Level 16, State 0, Line 1
The INSERT statement conflicted with the FOREIGN KEY constraint "FK__Employee__Depart__0519C6AF". The conflict occurred in database "Tinker_2", table "dbo.Department", column 'DepartmentID'.

Als ik een afdeling probeer te verwijderen via de weergave

DELETE FROM dbo.BlueDepartments
WHERE DepartmentID = 2

Wat resulteert in:

Msg 547, Level 16, State 0, Line 1
The DELETE statement conflicted with the REFERENCE constraint "FK__Employee__Depart__0519C6AF". The conflict occurred in database "Tinker_2", table "dbo.Employee", column 'DepartmentID'.

Dus beperkingen op onderliggende tabellen zijn nog steeds van toepassing.

Ik hoop dat dit helpt, maar misschien heb ik je probleem verkeerd begrepen.


10
2018-01-03 20:40



Als u probeert gegevens in te voegen, bij te werken of te verwijderen via een weergave, zijn de onderliggende tabelbeperkingen nog steeds van toepassing.


2
2017-12-18 14:58



Iets als dit in View2 is waarschijnlijk de beste keuze:

CREATE VIEW View2
AS
     SELECT
          T2.col1,
          T2.col2,
          ...
     FROM
          Table2 T2
     INNER JOIN Table1 T1 ON
          T1.pk = T2.t1_fk

1
2017-12-18 14:44



Als het omrollen van tabellen zodat Identiteitskolommen niet tegen elkaar botsen, zou een mogelijkheid zijn om een ​​opzoektabel te gebruiken die verwijst naar de verschillende gegevenstabellen op identiteit en een tabelreferentie.

Vreemde sleutels in deze tabel werken langs de lijn voor het verwijzen naar tabellen.

Dit zou op een aantal manieren duur zijn De referentiële integriteit op de opzoektabel zou moeten worden afgedwongen met behulp van triggers. Extra opslag van de opzoektabel en indexering naast de gegevenstabellen. Het lezen van gegevens zou bijna zeker een opgeslagen procedure of drie inhouden om een ​​gefilterde UNION uit te voeren. Queryplanevaluatie zou ook ontwikkelingskosten met zich meebrengen.

De lijst gaat maar er kunnen enkele scenario's werken.


1
2018-06-03 16:24



Het schema van Rob Farley gebruiken:

CREATE TABLE dbo.testtable(
id int IDENTITY(1,1) PRIMARY KEY,
val int NOT NULL); 
go
INSERT dbo.testtable(val)
VALUES(20),(30),(40),(50),(60),(70);
go 
CREATE TABLE dbo.secondtable(
id int NOT NULL,
CONSTRAINT FK_SecondTable FOREIGN KEY(id) REFERENCES dbo.TestTable(id)); 
go

CREATE TABLE z(n tinyint PRIMARY KEY);
INSERT z(n)
VALUES(0),(1);
go
CREATE VIEW dbo.SecondTableCheck WITH SCHEMABINDING AS
SELECT 1 n
FROM dbo.TestTable AS t JOIN dbo.SecondTable AS s ON t.Id = s.Id
CROSS JOIN dbo.z
WHERE t.Val < 50;
go
CREATE UNIQUE CLUSTERED INDEX NoSmallIds ON dbo.SecondTableCheck(n);
go

Ik moest een kleine helpertabel (dbo.z) maken om dit te laten werken, omdat geïndexeerde views geen self joins, outer joins, subqueries of afgeleide tabellen kunnen hebben (en TVC's tellen als afgeleide tabellen).


1
2018-01-27 21:30



Een andere benadering, afhankelijk van uw vereisten, zou zijn om een ​​opgeslagen procedure te gebruiken om twee recordsets te retourneren. U geeft de filtercriteria door en gebruikt de filtercriteria om query's uit te voeren op tabel 1, waarna deze resultaten kunnen worden gebruikt om de query te filteren op tabel 2, zodat de resultaten ook consistent zijn. Vervolgens geeft u beide resultaten terug.


0
2017-12-31 11:59



U kunt de gefilterde tabel 1-gegevens naar een andere tabel verplaatsen. De inhoud van deze verzameltabel is uw weergave 1 en vervolgens bouwt u weergave 2 via een samenvoeging van de verzameltabel en tabel 2. Op deze manier wordt het verwerken voor filtertabel 1 eenmaal uitgevoerd en opnieuw gebruikt voor beide weergaven.

Waar het eigenlijk op neer komt is dat view 2 geen idee heeft wat voor soort filteren je hebt uitgevoerd in weergave 1, tenzij je aan weergave 2 de filtercriteria vertelt, of het op de een of andere manier afhankelijk maakt van de resultaten van weergave 1, wat hetzelfde filteren betekent dat gebeurt op view1.

Beperkingen voeren geen enkele vorm van filtering uit, ze voorkomen alleen ongeldige gegevens of wijzigingen in de cascadesleutel en verwijderen.


0
2017-12-31 11:57



Nee, u kunt geen externe sleutels maken op weergaven.

Zelfs als je zou kunnen, waar zou dat je verlaten? Je zou nog steeds de FK moeten declareren nadat je de weergave hebt gemaakt. Wie zou de FK, u of de gebruiker aangeven? Als de gebruiker voldoende verfijnd is om een ​​FK te declareren, waarom kan hij dan geen inner join toevoegen aan de weergave waarnaar wordt verwezen? bijvoorbeeld:

create view1 as select a, b, c, d from table1 where a in (1, 2, 3)
go 
create view2 as select a, m, n, o from table2 where a in (select a from view1)
go 

vs:

create view1 as select a, b, c, d from table1 where a in (1, 2, 3)
go 
create view2 as select a, m, n, o from table2
--# pseudo-syntax for fk:
alter view2 add foreign key (a) references view1 (a)
go 

Ik zie niet in hoe de buitenlandse sleutel uw taak zou vereenvoudigen.

Alternatief:

Kopieer de subset met gegevens naar een ander schema of andere database. Dezelfde tabellen, dezelfde sleutels, minder gegevens, snellere analyse, minder conflicten.

Als u een subset van nodig hebt alle de tabellen, gebruik een andere database. Als u slechts een subset van nodig hebt sommige tabellen, gebruik een schema in dezelfde database. Op die manier kunnen uw nieuwe tabellen nog steeds verwijzen naar de niet-gekopieerde tabellen.

Gebruik vervolgens de bestaande weergaven om de gegevens te kopiëren. FK-overtredingen zullen een foutmelding geven en aangeven welke weergaven moeten worden bewerkt. Maak een baan en plan deze indien nodig dagelijks in.


0
2018-01-02 03:01



Vanuit een puur gegevensintegriteitsperspectief (en niets te maken met de Query Optimizer) had ik een geïndexeerde weergave overwogen. Ik dacht dat je er een unieke index op kon maken, die kan worden verbroken als je probeert gebroken integriteit te krijgen in je onderliggende tabellen.

Maar ... ik denk niet dat je de beperkingen van geïndexeerde gezichtspunten voldoende kunt omzeilen.

Bijvoorbeeld:

U kunt outer joins of subquery's niet gebruiken. Dat maakt het erg moeilijk om de rijen te vinden die niet in de weergave bestaan. Als u aggregaten gebruikt, kunt u HAVING niet gebruiken, dus dat schrapt enkele opties die u daar ook zou kunnen gebruiken. Je kunt zelfs constanten niet in een geïndexeerde weergave plaatsen als je een groep hebt (ongeacht of je een GROUP BY-component gebruikt), dus je kunt zelfs niet proberen een index op een constant veld te zetten, zodat een tweede rij omvalt. U kunt UNION ALL niet gebruiken, dus het idee om een ​​telling te hebben waardoor een unieke index wordt verbroken wanneer deze een tweede nul bereikt, werkt niet.

Ik heb het gevoel dat er een antwoord moet zijn, maar ik ben bang dat je je werkelijke ontwerp goed moet bekijken en moet uitzoeken wat je echt nodig hebt. Misschien triggers (en goede indexen) op de betreffende tabellen, zodat eventuele wijzigingen die iets kunnen breken, dat allemaal kunnen oprollen.

Maar ik hoopte echt iets te kunnen voorstellen dat de Query Optimizer mogelijk zou kunnen gebruiken om de prestaties van je systeem te verbeteren, maar ik denk niet dat ik het kan.


0
2018-01-04 07:16