Add Foreign Key Constraint - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

Add Foreign Key Constraint

Author

Topic

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-10 : 23:16:07

Hi,

I want to add Foreign key constraint to tbVessel
so that if Vessel that exist in other table can only be deleted from
tbVessel.

Got this error:
There are no primary or candidate keys in the referenced table 'dbo.tbDem' that match the referencing column list in the foreign key 'FK_tbVessel_tbDem'.
Could not create constraint. See previous errors.


alter table [dbo].[tbVessel]
with check add constraint [FK_tbVessel_tbDem]
foreign key ( [Vessel] )
references [dbo].[tbDem] ( [Vessel] )

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-06-11 : 00:12:50

Check the table, there is no primary in the table,
so, u can put a primary key or Vessel should be not null column in the tbdem

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 00:17:59

Is there primary key for vessel in the table tbdem or not

ALTER TABLE tbdem ADD CONSTRAINT pk_tbdem primary key (Vessel)

alter table tbVessel add constraint FK_tbVessel_tbDem foreign key (Vessel) references tbDem(Vessel)

melon.melon
Yak Posting Veteran

76 Posts

Posted - 2009-06-11 : 02:27:13

There is a primary key in tbDem.
Vessel is just a column in tbDem but in tbVessel
it is a primary.

tbVessel primary Key - Vessel
tbDem primary Key - ID

Can i do to tables that has null and non-null value for Vessel?

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-06-11 : 02:38:37

Vessel is just a column in tbDem then u cannot keep foriegn key with references of vessel in the table tbdem

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2009-06-11 : 11:29:33

quote:
Originally posted by melon.melon

There is a primary key in tbDem.
Vessel is just a column in tbDem but in tbVessel
it is a primary.

tbVessel primary Key - Vessel
tbDem primary Key - ID

Can i do to tables that has null and non-null value for Vessel?

which is master table containing vessel info? tbVessel or tbDem? i think you should be creating fk on tbDem rather than tbVessel

ie.
alter table [dbo].[tbDem]
with check add constraint [FK_tbVessel_tbDem]
foreign key ( [Vessel] )
references [dbo].[tbVessel] ( [Vessel] )

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources