videos
video collection of sqlschool.gr
Summary
Σε αυτό το επεισόδιο SQL Server in Greek δείχνουμε τι είναι unstrusted foreign key constraint πως δημιουργείται και πως το αποφεύγουμε
Video
Demo Code
create database demotrustconstraints;
go
use demotrustconstraints;
go
create table dbo.cars
(
carid int identity
, model nvarchar(20)
);
go
alter table dbo.cars
add constraint pk_cars primary key clustered (carid);
go
create table dbo.parts
(
partid int identity
, partdescription nvarchar(20)
);
go
alter table dbo.parts
add constraint pk_parts primary key clustered (partid);
go
create table dbo.carsparts
(
carid int not null
, partid int not null
, quantity int not null
);
go
alter table dbo.carsparts
add constraint pk_carsparts primary key clustered (carid,partid);
go
select c.model
from dbo.carsparts as cp
inner join dbo.cars as c on c.carid = cp.carid
inner join dbo.parts as p on p.partid = cp.partid;
go
alter table dbo.carsparts
add constraint fk_carspart_cars
foreign key (carid)
references dbo.cars;
go
alter table dbo.carsparts
add constraint fk_carspart_parts
foreign key (partid)
references dbo.parts;
go
select c.model
from dbo.carsparts as cp
inner join dbo.cars as c on c.carid = cp.carid
inner join dbo.parts as p on p.partid = cp.partid;
go
select concat(quotename(s.name),'.',quotename(o.name),'.', quotename(i.name)) as tablefkname, i.is_not_trusted
from sys.foreign_keys as i
inner join sys.objects as o on i.parent_object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id;
go
select concat(quotename(s.name),'.',quotename(o.name),'.', quotename(i.name)) as tableckname,i.is_not_trusted
from sys.check_constraints as i
inner join sys.objects as o on i.parent_object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id;
go
alter table dbo.carsparts nocheck constraint fk_carspart_cars;
go
alter table dbo.carsparts nocheck constraint fk_carspart_parts;
go
select concat(quotename(s.name),'.',quotename(o.name),'.', quotename(i.name)) as tablefkname, i.is_not_trusted
from sys.foreign_keys as i
inner join sys.objects as o on i.parent_object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id;
go
select concat(quotename(s.name),'.',quotename(o.name),'.', quotename(i.name)) as tableckname,i.is_not_trusted
from sys.check_constraints as i
inner join sys.objects as o on i.parent_object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id;
go
select c.model
from dbo.carsparts as cp
inner join dbo.cars as c on c.carid = cp.carid
inner join dbo.parts as p on p.partid = cp.partid
go
alter table dbo.carsparts check constraint fk_carspart_cars;
go
alter table dbo.carsparts check constraint fk_carspart_parts;
go
select concat(quotename(s.name),'.',quotename(o.name),'.', quotename(i.name)) as tablefkname, i.is_not_trusted
from sys.foreign_keys as i
inner join sys.objects as o on i.parent_object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id;
go
select concat(quotename(s.name),'.',quotename(o.name),'.', quotename(i.name)) as tableckname,i.is_not_trusted
from sys.check_constraints as i
inner join sys.objects as o on i.parent_object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id;
go
alter table dbo.carsparts with check check constraint fk_carspart_cars;
go
alter table dbo.carsparts with check check constraint fk_carspart_parts;
go
select concat(quotename(s.name),'.',quotename(o.name),'.', quotename(i.name)) as tablefkname, i.is_not_trusted
from sys.foreign_keys as i
inner join sys.objects as o on i.parent_object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id;
go
select concat(quotename(s.name),'.',quotename(o.name),'.', quotename(i.name)) as tableckname,i.is_not_trusted
from sys.check_constraints as i
inner join sys.objects as o on i.parent_object_id = o.object_id
inner join sys.schemas as s on o.schema_id = s.schema_id;
go
select c.model
from dbo.carsparts as cp
inner join dbo.cars as c on c.carid = cp.carid
inner join dbo.parts as p on p.partid = cp.partid
go