Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Can you have multiple foreign key constraints in a SQL create table statement?

Posted on 2011-02-25
3
Medium Priority
?
390 Views
Last Modified: 2012-05-11
I get the folowing error when the Booksregions create table scripts is executed.

Msg 8148, Level 16, State 0, Line 1
More than one column FOREIGN KEY constraint specified for column 'RegionID', table 'dbo.BooksRegions'.

The books and Regions tables are created just fine.

Can you have multiple foreign key constraints in a SQL create table statement?

How do i resolve this error?



CREATE TABLE [dbo].[Books](
      [ISBN] [char](20) NOT NULL Primary key,
      [AuthorID] [char](10) NOT NULL,
      [Title] [nvarchar](250) NOT NULL,
      [Copyright_Year] [char](4) NOT NULL,
      [PublisherID] [char](10) NOT NULL,
      [Language] [char](30) NOT NULL,
      [Price] [money] NOT NULL,
)

CREATE TABLE [dbo].[Regions](
      [RegionID] [char](10) NOT NULL,
      [RegionDescription] [varchar](100) NULL,
      [Country] [char](30) NOT NULL
Primary Key(RegionID)
)


CREATE TABLE [dbo].[BooksRegions](
      [ISBN] [char](20) NOT NULL ,
      [RegionID] [char](10) NOT NULL
CONSTRAINT [FK_BooksRegion_ISBN] FOREIGN KEY([ISBN])
REFERENCES [dbo].[Books] ([ISBN])
CONSTRAINT [FK_BooksRegion_RegionID] FOREIGN KEY([RegionID])
REFERENCES [dbo].[Regions] ([RegionID])
       )
0
Comment
Question by:wademi
3 Comments
 
LVL 9

Expert Comment

by:s_chilkury
ID: 34977667
0
 
LVL 22

Accepted Solution

by:
pivar earned 2000 total points
ID: 34977697
Hi,

I think your problem here is a missing comma between the constraints. Try this



/peter

CREATE TABLE [dbo].[Books](
      [ISBN] [char](20) NOT NULL Primary key,
      [AuthorID] [char](10) NOT NULL,
      [Title] [nvarchar](250) NOT NULL,
      [Copyright_Year] [char](4) NOT NULL,
      [PublisherID] [char](10) NOT NULL,
      [Language] [char](30) NOT NULL,
      [Price] [money] NOT NULL,
)

CREATE TABLE [dbo].[Regions](
      [RegionID] [char](10) NOT NULL,
      [RegionDescription] [varchar](100) NULL,
      [Country] [char](30) NOT NULL
Primary Key(RegionID)
) 


CREATE TABLE [dbo].[BooksRegions](
      [ISBN] [char](20) NOT NULL ,
      [RegionID] [char](10) NOT NULL
CONSTRAINT [FK_BooksRegion_ISBN] FOREIGN KEY([ISBN])
REFERENCES [dbo].[Books] ([ISBN]),
CONSTRAINT [FK_BooksRegion_RegionID] FOREIGN KEY([RegionID])
REFERENCES [dbo].[Regions] ([RegionID])
       )

Open in new window

0
 
LVL 39

Expert Comment

by:Pratima Pharande
ID: 34977766
try this
two comma's missing

CREATE TABLE [dbo].[BooksRegions](
      [ISBN] [char](20) NOT NULL ,
      [RegionID] [char](10) NOT NULL,
CONSTRAINT [FK_BooksRegion_ISBN] FOREIGN KEY([ISBN])
REFERENCES [dbo].[Books] ([ISBN]),
CONSTRAINT [FK_BooksRegion_RegionID] FOREIGN KEY([RegionID])
REFERENCES [dbo].[Regions] ([RegionID])
       )
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Your data is at risk. Probably more today that at any other time in history. There are simply more people with more access to the Web with bad intentions.
In today's business world, data is more important than ever for informing marketing campaigns. Accessing and using data, however, may not come naturally to some creative marketing professionals. Here are four tips for adapting to wield data for insi…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …

926 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question