Link to home
Start Free TrialLog in
Avatar of curiouswebster
curiouswebsterFlag for United States of America

asked on

What's wrong with this T-SQL Foreign Key?

The AuPair table has a non-primary key called AuPair. APNo.

I am getting incorrect syntax.

Suggestions?

Thanks!

USE [Tellus]
GO

IF OBJECT_ID('extension.ExtAPTransferToSF', 'U') IS NOT NULL 
  DROP TABLE extension.ExtAPTransferToSF;

  
/****** Object:  Table [extension].[ExtAPTransferToSF]    Script Date: 2/8/2017 2:10:18 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [extension].[ExtAPTransferToSF](
	[ExtAPTransferToSFId] [int] IDENTITY(1,1) NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[ExtensionSource] [nvarchar](25) NOT NULL, --AuPair/Hostfamily/IT User
	[CallingFunction] [nvarchar](255) NOT NULL,
	[APNo] [nvarchar](255) NOT NULL,
	[HostfamilyId] [nvarchar](255) NULL,
	[Status] [nvarchar](255) NOT NULL, --Success/Failure
	[Error] [nvarchar](1500) NULL,

    CONSTRAINT [FK_APNo_AuPair] 
	FOREIGN KEY ExtAPTransferToSF(APNo) REFERENCES AuPair(APNo),


	CONSTRAINT [PK_ExtAPTransferToSFID] PRIMARY KEY CLUSTERED
	(
		[ExtAPTransferToSFID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


) ON [PRIMARY]


GO

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of _agx_
_agx_
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of curiouswebster

ASKER

I changed the foreign key to be a unique ID, but still get this error:

Msg 156, Level 15, State 1, Line 43
Incorrect syntax near the keyword 'CONSTRAINT'.


CREATE TABLE [extension].[ExtAPTransferToSF](
	[ExtAPTransferToSFId] [int] IDENTITY(1,1) NOT NULL,
	[Timestamp] [datetime] NOT NULL,
	[ExtensionSource] [nvarchar](25) NOT NULL, --AuPair/Hostfamily/IT User
	[CallingFunction] [nvarchar](255) NOT NULL,
	[APNo] [varchar](255) NOT NULL,
	[HostfamilyId] [nvarchar](255) NULL,
	[Status] [nvarchar](255) NOT NULL, --Success/Failure
	[Error] [nvarchar](1500) NULL,

	CONSTRAINT [FK_APNo_AuPair] FOREIGN KEY (APNo) REFERENCES AuPair(AuPair_Id), 


	CONSTRAINT [PK_ExtAPTransferToSFID] PRIMARY KEY CLUSTERED
	(
		[ExtAPTransferToSFID] ASC
	)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]


) ON [PRIMARY]

Open in new window

SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks