What is difference between foreign key and referen

问题:

I am very confused about those two terms. Are they the same or different?

Some books and people say they are the same and others say they are different.

I tried but couldn't find a conclusive answer.

回答1:

I am supposing that you are talking about using the REFERENCES where the FOREIGN KEY keyword is not used, eg.

author_id INTEGER REFERENCES author(id)

... instead of ...

author_id INTEGER,
FOREIGN KEY(author_id) REFERENCES author(id)

The answer is, that it is simply shorthand syntax for the same thing. The main concern when altering between the two should be readability.

回答2:

"Reference key" isn't a normal technical term in relational modeling or in SQL implementation in US English.

A foreign key "references" a key in some other table; could that be where the confusion comes from?

回答3:

You don't really call something a reference key... They are the same thing... you might see the word references used for example in sqlite: you might use syntax like this to start a db of authors and books. This lets you show that one author can have many books. This tells the db that the books.author_id (defined a couple of lines up) references author.id

CREATE TABLE 'author' (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    firstname varchar(255)
    lastname varchar(255)
);

CREATE TABLE 'books' (
    id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
    author_id INTEGER,
    title varchar(255),
    published date,
    FOREIGN KEY(author_id) REFERENCES author(id)
);

回答4:

Perhaps you are using the term "reference key" somewhat loosely?

A foreign key value in one row is said to "reference" the row that contains the corresponding key value. Note the word "reference" in the prior sentence is a verb, so we may say we have a referencing foreign key value and a referenced key value.

Although it is the key values, rather than the table key constraint, that is being referenced, I suppose loosely speaking we could say "referenced key" to mean the rows that comprise the values that may potentially be referenced. I then see how "referenced key" could become "referenced key" but not belie its origin.

回答5:

A foreign key "references" a key in some other table. That key in some other table is called Referenced key. You'll probably hear a lot about this if you're using Graphic feature on phpmyadmin.

回答6:

A foreign key must refer to a primary key. When using REFERENCES constraint simply, then it isn't necessary that the referenced key be a primary key.

回答7:

There are 2 ways to declare a foreign key(s):

1. if the foreign key is a SINGLE attribute:
   REFERENCES ()

2. if foreign keys are a LIST of attributes

FOREIGN KEY () REFERENCES

回答8:

The only and most important difference between the two keywords 'FOREIGN KEY" and "REFERENCES" keywords is though both of them make the data to be child data of the parent table, the "FOREIGN KEY" is used to create a table level constraint whereas REFERENCES keyword can be used to create column level constraint only. Column level constraints can be created only while creating the table only. But table level constraints can be added using ALTER TABLE command.