Sql: how do I add a constraint foreign key

Question

0.00/5 (No votes)

See more:

This is the first time I use SQL, and I have a problem with adding a foreign key to my table.

I have 2 tables: ORDER_T (referenced) and ORDER_ITEM (referencing)

create table ORDER_T(
Id int primary key, 
OrderDate date,
TotalAmount int
);

create table ORDER_ITEM(
UnitPrice int,
Quantity int,
OrderID int,
ProductID int
);

This is my code to add a foreign key:

ALTER TABLE ORDER_ITEM
ADD FOREIGN KEY (OrderID) REFERENCES ORDER_T(Id);

But, it gives me this error:

Quote:
Error: near "FOREIGN": syntax error

Help?

What I have tried:

I tried to search online to figure out what I did wrong, or what is the meaning of the error, but to no avail.

Posted 22-Dec-23 1:43am

Rama2F

Add a Solution

Comments

0x01AA 22-Dec-23 8:07am

I tried your statements with MS SQL. For me it works fine...

2 solutions

Solution 2

You need to do the following:

SQL

ALTER TABLE TABLE2 
ADD CONSTRAINT[symbol] 
FOREIGN KEY(column_name) 
REFERENCES TABLE1(column_name);

Thus, your case:

SQL

ALTER TABLE ORDER_ITEM 
ADD CONSTRAINT FK_ORDERS 
FOREIGN KEY(OrderID) 
REFERENCES ORDER_T(Id);

Refer: SQL - Foreign Key[^]

Posted 22-Dec-23 2:02am

Sandeep Mewara

Comments

Rama2F 22-Dec-23 8:20am

I tried it, but it gave me the following error message: Error: near "CONSTRAINT": syntax error

Sandeep Mewara 22-Dec-23 9:29am

This works as is. Not sure what exactly you might be doing wrong post copy-pasting above.

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Richard Deeming · Accepted Answer · 2023-12-22T01:57:00

Solution 1

Create a foreign key in an existing table - Create Foreign Key Relationships - SQL Server | Microsoft Learn[^]

SQL

ALTER TABLE ORDER_ITEM
ADD CONSTRAINT FK_ORDER_ITEM_ORDER_T FOREIGN KEY (OrderID) REFERENCES ORDER_T(Id);

Posted 22-Dec-23 1:57am

Richard Deeming

Comments

0x01AA 22-Dec-23 8:05am

I tried OP's statements 1:1. For me everything works with them. Anyway a 5.

Rama2F 22-Dec-23 8:22am

I'm sorry, but I'm having difficulty understanding your comment, since English is not my first language. Are you saying that when copying my code, it does NOT give you an error? If so, could you please provide me the name of the compiler / program you are using? Thank you.

0x01AA 22-Dec-23 8:26am

You are correct. I copied your code and everything works fine, no error message.
I tried that with MSSQL Management Console. My server version is:
Microsoft SQL Server 2019 (RTM) - 15.0.2000.5 (X64)
Sep 24 2019 13:48:23
Copyright (C) 2019 Microsoft Corporation
Express Edition (64-bit) on Windows 10 Pro 10.0 <x64> (Build 19045: )

Rama2F 22-Dec-23 8:29am

Thank you.

Rama2F 22-Dec-23 8:17am

I tried it, but it gave me the following error message: Error: near "CONSTRAINT": syntax error

Richard Deeming 22-Dec-23 8:18am

Which DBMS are you using?

Rama2F 22-Dec-23 8:22am

SQL Server

Richard Deeming 22-Dec-23 8:25am

Well, that's the correct syntax for SQL Server.

I've just run this:

create table ORDER_T(
Id int primary key, 
OrderDate date,
TotalAmount int
); 

create table ORDER_ITEM(
UnitPrice int,
Quantity int,
OrderID int,
ProductID int
);

ALTER TABLE ORDER_ITEM
ADD CONSTRAINT FK_ORDER_ITEM_ORDER_T FOREIGN KEY (OrderID) REFERENCES ORDER_T(Id);

Commands completed successfully.
Completion time: 2023-12-22T13:24:36.4365613+00:00

Rama2F 22-Dec-23 8:27am

Then could you please provide me with the name of your compiler/program ? Thank you.

Dave Kreskowiak 22-Dec-23 9:53am

There is "compiler/program". SQL Servers are managed entirely by the SQL language they support. Not every SQL database engine supports the exact same SQL "dialect." What syntax you use on Oracle doesn't work on SQL Server, nor any other SQL database engine. The same goes for SQL Server, MySQL, SQLite, MongoDB, ... They all have their own SQL dialects which do not work on other engines.

Rama2F 22-Dec-23 8:25am

And I'm using " programiz Online SQL editor "

Richard Deeming 22-Dec-23 8:32am

Well, there's your problem; that editor is using SQLite, which is not Microsoft SQL Server.

Their documentation even points out that adding a foreign key constraint with ALTER TABLE is not supported in their editor:
SQL FOREIGN KEY Constraint (with examples)[^]
"Note: This action is not supported on our online SQL editor as it is based on SQLite."

Rama2F 22-Dec-23 8:35am

Thank you