What method should I use for creating foreign keys?

kristofer · #1 (**permalink**) 09-25-09, 14:28

Hi, I'm not very good at SQL and I would appreciate it if someone could explain why many of the tutorials I've come across seems to be using different approaches for creating foreign keys, is there one way that could be considered to be the "best" or are they all solutions for differentsituations? In the examples below are the most prevailing ways of creating foreign keys that I've seen (I've even seen all three used at the same time in the same schema...) Which method would you use and why?

Example 1:

Code:

CREATE TABLE Customers (
   id INTEGER NOT NULL PRIMARY KEY,
   ...
);

CREATE TABLE Orders (
   id INTEGER NOT NULL PRIMARY KEY,
   customer_id INTEGER NOT NULL,
   ...
   FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

Example 2:

Code:

CREATE TABLE Customers (
   id INTEGER NOT NULL PRIMARY KEY,
   ...
);

CREATE TABLE Orders (
   id INTEGER NOT NULL PRIMARY KEY,
   customer_id INTEGER NOT NULL,
   ...
);

ALTER TABLE Orders
ADD FOREIGN KEY (customer_id) REFERENCES Customers(id);

Example 3:

Code:

CREATE TABLE Customers (
   id INTEGER NOT NULL PRIMARY KEY,
   ...
);

CREATE TABLE Orders (
   id INTEGER NOT NULL PRIMARY KEY,
   customer_id INTEGER NOT NULL,
   ...
);

ALTER TABLE Orders
ADD CONSTRAINT customer_fk FOREIGN KEY (customer_id) REFERENCES Customers(id);

dportas · #2 (**permalink**) 09-25-09, 15:41

In the first two the end result is the same. It's just a matter of style. Foreign keys are often put in separate scripts to CREATE TABLE statements, especially in source control or where automated deployments are required.

Example 3 is certainly better practice however because the constraint is explicitly named whereas in the first two examples it isn't. Always name your constraints otherwise it makes them harder to modify and maintain later on.

kristofer · #3 (**permalink**) 09-25-09, 16:07

Quote:

Originally Posted by dportas

In the first two the end result is the same. It's just a matter of style. Foreign keys are often put in separate scripts to CREATE TABLE statements, especially in source control or where automated deployments are required.

Example 3 is certainly better practice however because the constraint is explicitly named whereas in the first two examples it isn't. Always name your constraints otherwise it makes them harder to modify and maintain later on.

Thank you for your reply. I'm still a little bit confused though as some people seems to treat constraints and foreign/primary keys as interchangeable concepts (I suspect they are not though?) My understanding is that costraints are used to apply limitations on columns. The third example is the one that seems overly complex to me: Why name the constraint customer_fk when there's already a perfectly servicable column name already (customer_id) to use as a foreign key? Is there some kind of internal representation (used by the dbms) we're creating when we do this? I guess I'm being slow but I can't think of any situation were having the customer_fk name would help me later on, it just makes the sql-syntax more complex and harder to read.

dportas · #4 (**permalink**) 09-25-09, 16:30

A constraint is any type of rule that is enforced in a database. Foreign keys and Candidate keys (including primary keys) are examples of two different kinds of constraint.

Quote:

Originally Posted by kristofer

Why name the constraint customer_fk when there's already a perfectly servicable column name already (customer_id) to use as a foreign key?

What if the foreign key consisted of more than one column? Or what if there was more than one constraint on the one column? In most DBMSs you cannot drop or modify a constraint except by name. So having a unique and identifiable name is usually important.

kristofer · #5 (**permalink**) 09-25-09, 16:44

Quote:

Originally Posted by dportas

A constraint is any type of rule that is enforced in a database. Foreign keys and Candidate keys (including primary keys) are examples of two different kinds of constraint.

What if the foreign key consisted of more than one column? Or what if there was more than one constraint on the one column? In most DBMSs you cannot drop or modify a constraint except by name. So having a unique and identifiable name is usually important.

Thanks! I think I understand now. If I'm creating a simple schema (2-3 tables) with singleton, surrogate keys then there's really no need to use the constraint keyword and I can go with 'ALTER TABLE TableX ADD FOREIGN KEY...' without risking any performance issues or problems down the road. As I prefer surrogate keys to natural ones, the problem of refering to multiple keys won't exist.

dportas · #6 (**permalink**) 09-25-09, 16:53

Naming isn't a performance issue, no. But as I already said, in many DBMSs you can't modify the constraint unless you know its name. Not knowing the name could present problems when it comes to deployment and change management. That's one reason why DBAs prefer to use strong naming conventions.

n_i · #7 (**permalink**) 09-25-09, 17:42

If you do not specify the constraint name the DBMS will generate one. You can always find it from the database catalog; however, it is much better to learn that the constraint "CUSTOMER_FK" has been violated than if the error message points to something like "SYS98479" or "SQL00093009203".

shammat · #8 (**permalink**) 09-25-09, 17:51

You can give the "inline" foreign keys a name as well

Code:

CREATE TABLE Orders (
   id INTEGER NOT NULL PRIMARY KEY,
   customer_id INTEGER NOT NULL,
   ...
   CONSTRAINT fk_orders_cust 
       FOREIGN KEY (customer_id) REFERENCES Customers(id)
);

All versions have the same effect.
There is no functional difference between declaring an "inline" FK constraint or adding a constraint later via ALTER TABLE.

kristofer · #9 (**permalink**) 09-25-09, 18:09

I appreciate the input. Here's a schema I've been working on:

Code:

CREATE TABLE Patients (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
country_id INTEGER NOT NULL,
personal_number CHAR(10),
first_name VARCHAR(20),
last_name VARCHAR(30),
address VARCHAR(30),
postal_number CHAR(5),
city VARCHAR(20),
phone CHAR(10),
email VARCHAR(50),
);

CREATE TABLE Entries (
id INTEGER NOT NULL PRIMARY KEY AUTOINCREMENT,
patient_id INTEGER NOT NULL,
title VARCHAR(30),
category VARCHAR
created DATE,
updated DATE,
entry TEXT,
);

CREATE TABLE Countries (
id INTEGER NOT NULL PRIMARY KEY,
country VARCHAR(30)
);

ALTER TABLE Patients 
ADD CONSTRAINT patients_fk FOREIGN KEY (country_id) REFERENCES Countries(id) ON DELETE SETT NULL;

ALTER TABLE Entries
ADD CONSTRAINT entries_fk FOREIGN KEY (patient_id) REFERENCES Patients(id) ON DELETE CASCADE;

INSERT INTO Countries (country) VALUES ('Sverige'), ('Finland'), ('Norge'), ('Danmark');

I guess I'll have to get used to using constraints then seeing as you all like them so much

P.S. What do you think about the notation I'm using? Do you write your sql differently (use uppercase/lowercase only or use different naming conventions for keys)?

shammat · #10 (**permalink**) 09-25-09, 18:20

As you can see from my example I try to include both tables in the FK name (in the order how the FK is defined). This makes hunting through error messages a lot easier, especially if a table has a foreign key to more than one other table.

I have even seen teams where NOT NULL constraints where given a name, so that the application could easily analyse the error messages in a semi-automated way.

dportas · #11 (**permalink**) 09-25-09, 18:29

Quote:

Originally Posted by kristofer

I appreciate the input. Here's a schema I've been working on:

Are you serious or is that just a hypothetical exercise? In the real world I can see loads of problems with what you have: missing keys; nullable attributes that make little sense (patients without names?); single address per person; likely standards and/or regulatory compliance issues. I hope it's just a fictional example but in any case these are just some of the things you ought to be thinking about. Hope this helps.

kristofer · #12 (**permalink**) 09-25-09, 18:32

Quote:

Originally Posted by shammat

As you can see from my example I try to include both tables in the FK name (in the order how the FK is defined). This makes hunting through error messages a lot easier, especially if a table has a foreign key to more than one other table.

I have even seen teams where NOT NULL constraints where given a name, so that the application could easily analyse the error messages in a semi-automated way.

I see, but in your example above wouldn't it be more consistent if you used something like

Code:

CONSTRAINT pk_orders PRIMARY KEY (id)

?

kristofer · #13 (**permalink**) 09-25-09, 18:40

Quote:

Originally Posted by dportas

Are you serious or is that just a hypothetical exercise? In the real world I can see loads of problems with what you have: missing keys; nullable attributes that make little sense (patients without names?); single address per person; likely standards and/or regulatory compliance issues. I hope it's just a fictional example but in any case these are just some of the things you ought to be thinking about. Hope this helps.

It's just an java example I'm testing things out on. However:

"Patients without names" Huh? Are you perhaps refering to that when a Patient is deleted I'm setting the Country to null?

"Missing keys" Don't know what you're talking about. If you're refering to natural keys then I've already stated that I prefer to use surrogate keys and I certainly have enough of these.

"Single address per person" What about it? A person rarely lives at more than one place at a time and he/she can only be registered at one address.

shammat · #14 (**permalink**) 09-25-09, 18:42

Quote:

Originally Posted by kristofer

I see, but in your example above wouldn't it be more consistent if you used something like

Code:

CONSTRAINT pk_orders PRIMARY KEY (id)

?

Definitely!
In the example I just wanted to show that you can name an inline constraint, the rest was just copied from your code.

dportas · #15 (**permalink**) 09-25-09, 18:52

Quote:

Originally Posted by kristofer

"Patients without names" Huh? Are you perhaps refering to that when a Patient is deleted I'm setting the Country to null?

No, I meant: why do you allow the patient's name to be null?

Quote:

"Missing keys" Don't know what you're talking about. If you're refering to natural keys then I've already stated that I prefer to use surrogate keys and I certainly have enough of these.

But you've allowed duplicate countries and duplicate patients. Irrespective of whether you use surrogate keys or not, users always retrieve data by the natural key attributes. A surrogate key therefore requires a natural key as well.

Quote:

"Single address per person" What about it? A person rarely lives at more than one place at a time and he/she can only be registered at one address.

I don't think I've ever seen a real system that only allowed one address per person. Even if that was a requirement it would probably still be a good idea to allow more than one person to share the same address via a separate address table.

Internet Services

Web Development

Digital Marketing

Consumer Tech