CODEWITHSUNDEEP
mysqlsqlconstraints
tsen0406
tsen0406

Reputation: 119

Confused about foreign key constraint

I have a general question about constraint. What are the difference between the following examples?

CREATE TABLE Orders (
    OrderID int NOT NULL PRIMARY KEY,
    OrderNumber int NOT NULL,
    PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);

CREATE TABLE Orders (
    OrderID int NOT NULL,
    OrderNumber int NOT NULL,
    PersonID int,
    PRIMARY KEY (OrderID),
    CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
    REFERENCES Persons(PersonID)
);

Thank you!

Upvotes: 1

Views: 1112

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562931

There is no logical difference.

Standard SQL supports both forms of declaring constraints: at the column level, as in your first example, and at the table level, in your second example.

Table level constraint syntax is needed if you have a primary key or foreign key that involves more than one column.

MySQL supports both column-level and table-level syntax for PRIMARY KEY. But if you subsequently run SHOW CREATE TABLE Orders you will see that MySQL reports it back as if it was declared as a table-level constraint.

MySQL supports only table-level syntax for FOREIGN KEY.

It has been a long-time feature request to support column-level FOREIGN KEY syntax, but so far it has not been implemented. https://bugs.mysql.com/bug.php?id=4919

Upvotes: 1

Zohar Peled
Zohar Peled

Reputation: 82534

In the first example, The database will name the constraints implicitly.
In the second example, the create table statement sets the name of the foreign key constraint explicitly. (the primary key should also be named but it's not in this example)

As best practice, you should always give your constraints meaningful names.

Upvotes: 1

Related Questions