utilisation de l'auto-référencement dans le serveur sql

Question

create table EMP(Eid int primary key)

insert into EMP values(11e3)

--auto-référencement

alter table EMP 
add constraint fk_EMP_Eid 
foreign key (Eid) references EMP(Eid)

-- maintenant insérer

insert into EMP values(12e2)

Mais, cette insertion devrait échouer, parce qu'il n'y a aucune valeur précédente de Eid=1200 dans la table EMP, donc quand la clé étrangère fera référence à cette colonne, elle ne trouvera pas la valeur, et donc l'insertion échouera.

mais pourquoi ce succès ?

Answer 1

La colonne se référence elle-même.

Ainsi, l'ajout de la ligne elle-même garantit qu'il existe une ligne correspondante. Cette contrainte ne peut jamais échouer.

En fait, en regardant le plan d'exécution, SQL Server s'en rend compte et ne prend même pas la peine de le vérifier. Il n'y a pas de assert opérateur présent.

Si nous créons une table Employé plus typique, il existe différents plans pour les insertions qui peuvent violer la contrainte comme ci-dessous.

create table EMP2(Eid int primary key, boss_id int null);
alter table EMP2 add constraint fk_EMP2_Eid 
 foreign key (boss_id) references EMP2(Eid)

insert into EMP2 values(1,null) /*Can't violate constraint as NULL*/
insert into EMP2 values(2,1)    /*Can violate constraint as NOT NULL*/

Si vous essayez plusieurs rangées d'un bobine de blocage est ajouté au plan afin que les contraintes ne soient pas vérifiées avant que toutes les lignes soient insérées.

insert into EMP2 values (3,2),(4,3) /*Can violate constraint - multiple rows*/

Et juste pour être complet, comme cela a été soulevé dans les commentaires, en regardant le cas où l'insertion est dans une table avec un FK faisant référence à une autre table...

CREATE TABLE EmpSalaryHistory
(
Eid INT NOT NULL REFERENCES EMP(Eid),
EffectiveDate DATETIME NOT NULL,
Salary INT,
PRIMARY KEY (Eid,EffectiveDate)
)

INSERT INTO EmpSalaryHistory
VALUES    (1,GETDATE(),50000),
          (2,GETDATE(),50000)

Dans ce cas, aucun spool n'est ajouté au plan, il peut vérifier l'insertion de chaque ligne plutôt que de tout insérer à la fin, ce qui lui permet de revenir en arrière plus tôt en cas d'échec d'une ligne (le résultat final sera le même).

Answer 2

Votre colonne FK fk_EMP_Eid autorise probablement les nuls, donc la relation n'est pas obligée d'exister, mais si vous essayez de mettre une valeur dans cette colonne, alors le serveur SQL vérifiera que le FK est valide ou sinon il émettra une erreur.

Answer 3

J'ai créé cet exemple de clé d'auto-référence pour ms sql server.

CREATE TABLE Category (
   CategoryId int IDENTITY(1,1) not null,
   ParentId int null,
   CONSTRAINT PK_CategoryId PRIMARY KEY CLUSTERED (CategoryId),
   CONSTRAINT FK_ParentId FOREIGN KEY (ParentId) REFERENCES  Category(CategoryId),
   Title nvarchar(255) NOT NULL
);

insert into category(title)
values
('category1');

insert into category(title,parentid)
values
('category2',1);