Using ADD CONSTRAINT vs drag in diagram

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

Using ADD CONSTRAINT vs drag in diagram

Author

Topic

newbie999
Starting Member

3 Posts

Posted - 2009-09-07 : 15:55:12

Hi experts
Im confused and need your help

I have two tables with "one to many".
One Artist can have zero or many Albums
(This was the simplest example I could figure out)
So:
Artist
Id (PK)
Name nvarchar
Age int

Album
Id (PK)
Name nvarchar
ReleaseYear int
ArtistId int

In my diagram I can drag between Artist.Id and Album.ArtistId to
create a relationship.

If I then create a script of the table Album i get this at the
end of the file:


ALTER TABLE [dbo].[Album] WITH CHECK ADD CONSTRAINT [FK_Album_Artist] FOREIGN KEY([ArtistId])
REFERENCES [dbo].[Artist] ([Id])
GO 
ALTER TABLE [dbo].[Album] CHECK CONSTRAINT [FK_Album_Artist]

I need to learn how to create the relationship with t-sql.
So I deleted my relation between the two tables and saved the diagram. Then I ran the code above and got:
A fatal scripting error occurred. Incorrect syntax was encountered while parsing GO.

So I then tried the three first lines such as:


ALTER TABLE [dbo].[Album] WITH CHECK ADD CONSTRAINT [FK_Album_Artist] FOREIGN KEY([ArtistId])
REFERENCES [dbo].[Artist] ([Id])

Finally I get "Command(s) completed successfully."

But when I check my diagram. (I created a new one)
There are no relation in my diagram.

If I run the same code again, I get:
"Msg 2714, Level 16, State 4, Line 1
There is already an object named 'FK_Album_Artist' in the database.
Msg 1750, Level 16, State 0, Line 1
Could not create constraint. See previous errors."

Where is the relation hiding?
Why cant I see it in the diagram?

So, creating a relation in the diagram is not the same as
using t-sql?

Many thanks

newbie999
Starting Member

3 Posts

Posted - 2009-09-08 : 15:35:48

Ahhhhhh, I just refreshed again and refreshed the window just before choosing which tables in my diagram.
All good....

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources