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