0

I hava known that foreign key is a constraint and references is a clause.

This also confuse me.Although it is mysql but not apache derby

This tells me how foreign key work but still confused for me I found out the answer also has the command like what i said references without foreign key

But my question is what is the point if you can create table which having references column to the other table without using foreign key. the command below still work

create table newTable(ColumnName varchar(100) references oldTable(ColumnName))

the foreign key example at the bottom of this document use foreign key to make references

Can anyone please help me out that differences between using references with foreign key and not using foreign key

I found out in SQLite if you references without foreign key it is same as references with foreign key is apache derby run in same way?

Loord
  • 3
  • 2
  • Are you asking why there are multiple syntax variants for declaring foreign key referential integrity relationships? If not, please clarify what you are asking. – Bryan Pendleton Jul 18 '18 at 19:41
  • Thanks for your response. I am confusing that what will it cause on derby if i use references without foreign key. Is it same as when i use both of them? – Loord Jul 19 '18 at 00:40
  • There are various types of foreign key constraints, and, unfortunately, there are also various syntax variants, which allow multiple ways of specifying the same foreign key constraints. I think that the best way for you to get a very precise answer to your question is for you to specify **exactly** the DDL commands you are issuing; provide as much detail as you can, and that gives you the best chance of getting a useful response. – Bryan Pendleton Jul 19 '18 at 03:04
  • One specific difference between a column-level referential integrity constraint and a table-level referential integrity constraint is that a column level constraint only relates one single column to another single column, whereas a table-level referential integrity constraint can relate multiple columns of one table to multiple columns of another table. See: https://db.apache.org/derby/docs/10.14/ref/rrefsqlj13590.html – Bryan Pendleton Jul 19 '18 at 03:07
  • 1
    Thank you for your help and patient . I just start learning apache.I have seen the document before and it makes me more confuse .I saw example for foreign key at the bottom of the document and I thought what if i give the column references without foreign key. And it still work! This make me confuse that is it adding foreign key by default for me or it is totally different meaning.Maybe i have misunderstanding on foreign key and references actually meaning and usage. – Loord Jul 19 '18 at 03:23

1 Answers1

2

A "foreign key" is the set of attributes of the referencing table. (In many implementations, one would have to speak of "ordered set of attributes" because many implementations make the order matter too, but that's a deviation from the theory.)

A referential integrity constraint is the rule as such to the effect that the [combination of] values appearing in the foreign key attributes [in the referencing table] must appear as [a combination of] values in the key attributes [in the referenced table]. ("key attributes" is a usage of the term "key" that is in line with relational theory. They might have been declared by means of a UNIQUE clause instead of a KEY clause, but that's just syntax. In the theory, everything that's declared unique is a key.)

The "REFERENCES" clause is the [most popular / most used] means to declare the referential integrity constraint. It's a syntactic means. It always includes a specification of the foreign key, but additionally it also defines the referenced table and its referenced attributes.

Because it is so exceptionally rare that there are two distinct referential integrity constraints based on the SAME foreign key (e.g. to two distinct referenced tables), it is quite common for all those terms to be used interchangeably.

Erwin Smout
  • 18,113
  • 4
  • 33
  • 52
  • Thanks for your response.Is it mean as long as the moment when I use references without foreign key, it has same meaning as using foreign key with references?(most of the time)I saw you said references always includes a specification of the foreign key .Is it mean that it helps you adding foreign key by default in command or it has same meaning. – Loord Jul 19 '18 at 10:46
  • 1
    It is very difficult to pin down what you are asking. If you declare a foreign key constraint (aka referential integrity constraint) then by definition you have to define one way or the other what the foreign key is, and if there's a foreign key somewhere then by definition that can only be in the context / as a consequence of the existence of a referential integrity constraint. Don't stare yourself blind on the ***syntactic means*** to achieve these. – Erwin Smout Jul 19 '18 at 10:52
  • 2
    The REFERENCES clause is the eldest way to declare a referntial integrity constraint and the corresponding foreign key. The CONSTRAINT clause is a more recent feature of the SQL language attempting to unify (the syntactic form for) specification of all kinds of supported constraints. If and when used for the creation of a referential integrity constraint, its effects are/should be totally indistinguishable compared to the constraint being created using the REFERECES clause. It's all just syntax. If that's what your question was about. – Erwin Smout Jul 19 '18 at 10:59
  • Thanks for your patient and information.I think i almost got it.You are right, I focus to much on syntactic means and definition.Thanks for your help. – Loord Jul 19 '18 at 11:20