Synonym in pk-fk - 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

General SQL Server Forums

New to SQL Server Programming

Synonym in pk-fk

Author

Topic

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-21 : 14:39:59

Can we use a column in synonym to create FK in another table? Thanks!

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-21 : 15:06:23

Synonyms don't affect columns or foreign key constraints. I'm not clear what your question means.

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-21 : 15:08:29

quote:
Originally posted by robvolk

Synonyms don't affect columns or foreign key constraints. I'm not clear what your question means.

Sorry I was not clear. I was creating foreign key constraint in a table and the FK column was referencing a column Synonym... Can we create that relationship in SQL 2008...

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-21 : 15:17:12

You mean the column is named "Synonym"? If so, yes, you can create a foreign key between tables with that column name. Make sure the referenced (parent) table has a unique constraint/index or primary key on that column though.

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-21 : 15:20:05

quote:
Originally posted by robvolk

You mean the column is named "Synonym"? If so, yes, you can create a foreign key between tables with that column name. Make sure the referenced (parent) table has a unique constraint/index or primary key on that column though.

Column is not named synonym. The referenced table is a Synonym.

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-07-21 : 15:35:32

No, because a synonym could refer to an object that's not in the same database, and foreign keys can only reference objects in the same DB. Even then it doesn't work:

CREATE TABLE a(a INT NOT NULL PRIMARY KEY)
GO
CREATE TABLE b(a INT NOT NULL)
GO
CREATE SYNONYM c FOR a
GO
ALTER TABLE b ADD CONSTRAINT fk_c FOREIGN KEY(a) REFERENCES c(a)  -- fails
GO

ALTER TABLE b ADD CONSTRAINT fk_a FOREIGN KEY(a) REFERENCES a(a)  -- succeeds
GO

exec sp_help 'a'
exec sp_help 'c'

DROP SYNONYM c
DROP TABLE b, a

Ghanta
Yak Posting Veteran

96 Posts

Posted - 2011-07-21 : 15:39:37

Yes! Thanks robvolk.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources