Where to state a foreign key when creating a table?

Question

I would like to design a table named arguments whose an attribute name is linked to another attribute name in a table called names.

I see two ways to express it in SQL:

by creating a constraint on the table:

CREATE TABLE names (   name text UNIQUE,
                      short text UNIQUE,
                    comment text);
CREATE TABLE arguments (   name text UNIQUE,
                        comment text,
                        FOREIGN KEY (name) REFERENCES names (name));

by qualifying the attribute on-the-fly:

CREATE TABLE names (   name text UNIQUE,
                      short text UNIQUE,
                    comment text);
CREATE TABLE arguments (   name text UNIQUE REFERENCES names (name),
                        comment text);

I would like to know:

if one of the two is commonly known as better than the other, and
if it can have consequences that I should be aware of.

Thank you for your help.

Using text data as a key is a bad practice. Your name table should have an ID column, this then becomes the FK for any references. — Maess, Dec 08 '11 at 14:00
If this is just proof of concept, then it doesn't matter. Get the proof done, and then do the actual work. Don't waste time solving problems that don't exist _right now_. — cdeszaq, Dec 08 '11 at 14:08
@cdeszaq - Although the sentiment of "don't let things stop you just getting it done" has great merit, I would not use it as a reason to avoid good practice (such as keeping identifiers and data as separate entities). Getting it done *as well as reasonably possible* can and does make life MUCH easier if and when you come to productionising the proof of concept. PART of the proof-of-concept should often be the software architecture. — MatBailie, Dec 08 '11 at 14:13
@Dems - I agree 100% with having a surrogate ID in _all_ cases, and that aspect of design _should_ be included in a proof-of-concept, but the issue at hand (how to declare a FK relationship) is not something that a PoC needs to even have on it's radar (in general). — cdeszaq, Dec 08 '11 at 14:16
We've had a couple of similar questions recently [here](http://stackoverflow.com/questions/8421055/correct-way-to-create-a-table-that-references-variables-from-another-table/8428298#8428298) and [here](http://stackoverflow.com/questions/8315628/what-is-foreign-key-is-for/8323581#8323581). — onedaywhen, Dec 08 '11 at 15:14
@onedaywhen Thanks for the links. It adds some details to the explanations I already got here. — Thibaud, Dec 08 '11 at 15:26

score 2 · Answer 1 · answered Dec 08 '11 at 14:03

These are just different syntax for the same end result.

Either is appropriate, but the former is a more common style in my experience. This may simply be to allow the human mind to more easily digest all the information. First describe the data, second describe how it relates to the rest of the world.

One comment I would make though, is that is more common to have IDs as unique identifiers and references. This allows you to change the Value in the Name field without changing it's Identity and breaking Referential Integrity. There are databases that can cascade such changes and update all occurrences of the Name, but in general it's considered cleaner to have Identifiers that are Separate from the Data.

Raihan · Accepted Answer · 2011-12-08T14:40:36.240

While the first option is known as out-of-line constraint declaration and the second option is in-line, both of them are functionally same.

What would be better is to assign a name to the foreign key constraint. If you have a name, you can selectively enable and disable the constraint if required.

Create table

CREATE TABLE arguments 
(   
    name text UNIQUE,                         
    comment text,                         
    constraint arguments_fk FOREIGN KEY (name) REFERENCES names (name)
);

Disable constraint

ALTER TABLE arguments NOCHECK CONSTRAINT arguments_fk;

Enable constraint

ALTER TABLE arguments CHECK CONSTRAINT arguments_fk;

This is for SQL Server. Oracle has equivalent commands.

score 0 · Answer 3 · answered Dec 08 '11 at 14:02

0

Use a foreign key. If you later find you have a performance problem (a measurable problem), then you can change it up to be different.

Keep things simple at first and get the product into the users' hands as fast as possible. Don't optimize things that you can't prove need it.

answered Dec 08 '11 at 14:02

cdeszaq

30,869
25
117
173

Both of these Are examples for Foreign Key Relationships. – MatBailie Dec 08 '11 at 14:04

Where to state a foreign key when creating a table?

3 Answers3