Postgres-XC 1.1 Documentation | ||||
---|---|---|---|---|
Prev | Up | Chapter 3. Advanced Features | Next |
Note: The following description applies both to Postgres-XC and PostgreSQL if not described explicitly.
Recall the weather
and
cities
tables from Chapter 2. Consider the following problem: You
want to make sure that no one can insert rows in the
weather
table that do not have a matching
entry in the cities
table. This is called
maintaining the referential integrity of
your data. In simplistic database systems this would be
implemented (if at all) by first looking at the
cities
table to check if a matching record
exists, and then inserting or rejecting the new
weather
records. This approach has a
number of problems and is very inconvenient, so
Postgres-XC can do this for you.
The new declaration of the tables would look like this:
CREATE TABLE cities ( city varchar(80) primary key, location point ); CREATE TABLE weather ( city varchar(80) references cities(city), temp_lo int, temp_hi int, prcp real, date date );
Now try inserting an invalid record:
INSERT INTO weather VALUES ('Berkeley', 45, 53, 0.0, '1994-11-28');
ERROR: insert or update on table "weather" violates foreign key constraint "weather_city_fkey" DETAIL: Key (city)=(Berkeley) is not present in table "cities".
The behavior of foreign keys can be finely tuned to your application. We will not go beyond this simple example in this tutorial, but just refer you to Chapter 5 for more information. Making correct use of foreign keys will definitely improve the quality of your database applications, so you are strongly encouraged to learn about them.
Note: The following description applies only to Postgres-XC
Please note that primary key and reference key are both allowed only when these columns are distribution keys when tables are distributed. As a default, Postgres-XC distributes each row of tables based upon the value of the first column of the table. You can choose any column as a basis of table distribution, or you can have copies of a table in all the Datanodes.
Please refer to SELECT for details.