---创建表a
If exists(select * from sysobject where [name]=='a' and xType = 'u')
Begin
Drop table aa
End
Begin
Create table aa
(id int,
cplpdid int,
[name] nchar(50),
Constraint pk_id Primary key Clustered(id),)
Insert into aa
select 1,3,'s'
union all
select 2,4,"w"
union all
select 3.5,'Q'
union all
select 4,6,'D'
End
IF EXISTS (select * from sysobjects where [name] = 'bb' and xtype='U') BEGIN
DROP TABLE bb
END
BEGIN
CREATE TABLE bb
(
cplpdid INT,
[user] NVARCHAR(50),
CONSTRAINT cplpdid PRIMARY KEY CLUSTERED(cplpdid)
)
INSERT into bb
SELECT 1,'aaa'
UNION ALL
SELECT 2, 'bbb'
UNION ALL
SELECT 3,'ccc'
UNION ALL
SELECT 4,'ddd'
end
Alter table aa with nocheck add constraint FK_aa_cplpdid Foreign key (cplpdid) Reference bb (cplpdid)
select * from aa where not exists (select cplpdid from bb where bb.cplpdid = a.cplpid)
select * from aa where aa.cplpdid not in(select cplpdid from bb)