CODEWITHSUNDEEP
sql-serverforeign-keys
Jason Vondersmith
Jason Vondersmith

Reputation: 147

Create SQL Table with FK

I'm trying to create a table with a couple columns having the same FK constraint. What is wrong with my syntax? I'm receiving a msg 102, incorrect syntax error around "fk_fighterID"

"FIGHTERS" is a table that has been set up with "fighterID" as the primarykey

CREATE TABLE FIGHTS
(
FIGHTID INT PRIMARY KEY, 
CONSTRAINT fk_fighterID FOREIGN KEY REFERENCES FIGHTERS(fighterID),
FIGHTER_ID_CHALLANGER int fk_fighterID,
FIGHTER_ID_CHALLANGED int fk_fighterID, 
CONSTRAINT fk_weightclass FOREIGN KEY (classID) REFERENCES WEIGHT_CLASSES 
      (classID)
WEIGHTCLASS NOT NULL fk_wightclass, 
IS_MAIN BIT NOT NULL,
IS_CO_MAIN BIT NOT NULL,
SCHEDULED ROUNDS TINYINT(1),
CONSTRAINT fk_WINMETH FOREIGN KEY (WINID) REFERENCES WINNING_METHODS (WINID)
WINNING METHOD NOT NULL fk_WINMETH
)
;

Upvotes: 0

Views: 300

Answers (2)

Jerry
Jerry

Reputation: 61

Correct Statement: CONSTRAINT fk_weightclass FOREIGN KEY (classID) REFERENCES WEIGHT_CLASSES (classID)

Incorrect Statement: CONSTRAINT fk_fighterID FOREIGN KEY REFERENCES FIGHTERS(fighterID),

You forgot to mention something like '(classId)'.

Edit: I am not sure what you are trying to achieve, but your above query is wrong, try below query if it makes sense.

CREATE TABLE FIGHTS ( FIGHTID INT PRIMARY KEY, FIGHTER_ID_CHALLANGER INT, FIGHTER_ID_CHALLANGED INT, IS_MAIN BIT NOT NULL, IS_CO_MAIN BIT NOT NULL, SCHEDULED_ROUNDS TINYINT(1), WEIGHTCLASS INT NOT NULL, CLASS_ID INT NOT NULL, WINNING_METHOD INT NOT NULL, CONSTRAINT fk_fighterID FOREIGN KEY (FIGHTER_ID_CHALLANGER) REFERENCES FIGHTERS (fighterID), CONSTRAINT fk_weightclass FOREIGN KEY (CLASS_ID) REFERENCES WEIGHT_CLASSES (classID), CONSTRAINT fk_WINMETH FOREIGN KEY (WINNING_METHOD) REFERENCES WINNING_METHODS (WINID) );

Upvotes: 1

thepiyush13
thepiyush13

Reputation: 1331

Shouldn`t the constraints be outside of the table creation statement like this:

CREATE TABLE Sales.TempSalesReason (TempID int NOT NULL, Name nvarchar(50), 
CONSTRAINT PK_TempSales PRIMARY KEY NONCLUSTERED (TempID), 
CONSTRAINT FK_TempSales_SalesReason FOREIGN KEY (TempID) 
    REFERENCES Sales.SalesReason (SalesReasonID) 
    ON DELETE CASCADE
    ON UPDATE CASCADE
);

Upvotes: 0

Related Questions