Open Source RDBMS - Seamless, Scalable, Stable and Free

한국어 | Login |Register

How to Get Foreign Keys


Getting Foreign Keys Using SHOW CREATE TABLE

Since CUBRID 9.0, CUBRID supports SHOW CREATE TABLE statement. This command allows you to see all the table columns as well as all the constraints such as primary key or foreign key. Below you can see the SHOW CREATE TABLE result ran on the "game" table from demo database:

csql> show create table game;
=== <Result of SELECT Command in Line 1> ===
  TABLE                 CREATE TABLE        
============================================
  'game'                'CREATE TABLE game (
host_year INTEGER NOT NULL, event_code INTEGER NOT NULL, athlete_code INTEGER NOT NULL, 
stadium_code INTEGER NOT NULL, nation_code CHARACTER(3), medal CHARACTER(1), game_date DATE,  
CONSTRAINT pk_game_host_year_event_code_athlete_code PRIMARY KEY  (host_year, event_code, athlete_code),  
CONSTRAINT fk_game_event_code FOREIGN KEY  (event_code) REFERENCES event ON DELETE RESTRICT ON UPDATE RESTRICT,  
CONSTRAINT fk_game_athlete_code FOREIGN KEY  (athlete_code) REFERENCES athlete ON DELETE RESTRICT ON UPDATE RESTRICT)'
1 row selected.
Current transaction has been committed.
1 command(s) successfully processed.

From the above output, you can see that pk_game_host_year_event_code_athlete_code is a primary key on host_year, event_code and athlete_code fields, while fk_game_event_code and fk_game_athlete_code are foreign keys referencing the event_code field in event table and athlete_code in athlete table respectively.

Getting Primary Keys Using db_index and SHOW INDEX

Another approach that works regardless of CUBRID version is described in the following section. Use the command below.

SELECT * FROM db_index WHERE class_name = 'game' AND is_primary_key = 'YES';

foreign_key.png

There are 2 foreign keys: fk_game_athlete_code. To find the columns constrained by a given foreign (or primary) key, run the SHOW INDEX command and look for the Key_name that corresponds to the above index_name.

show_index.png

As you can see, the 2 constrained columns are athlete_code and event_code. Currently, there is no way to obtain the referenced table name in SQL apart from using "SHOW CREATE TABLE" statement.

comments powered by Disqus
Page info
viewed 3753 times
translations en
Author
posted 2 years ago by
CUBRID
Contributors
updated 2 years ago by
View revisions
Share this article