Primary Key, Foreign Key, Constraint ( Interbase Tutorial # 7 )

Every table must have a primary key. Only one primary key can be created in a table. But we may have a collective combination of some columns to form a primary key. Primary key has to be a column which has a unique value. In our case, ‘employee’ table also must have a primary key. The column which has a unique value is ‘id’ column. 
The better way to create ‘employee’ table with a primary key is :

(Note : before you execute the SQL codes below, you have to drop your previous table in the database)

   create table employee(
   id domain_id not null primary key,
   name string30 not null,
   age domain_age not null,
   phonenumber string12 not null);

a table can also has a foreign key. Foreign key is a column or a combination of some columns in a table. The value must refer to the primary key which is in the table or other table. Now we’re going to create a table which has a foreign key refers to the primary key of ‘employee’ table.

 create table salary(
 salary_id domain_id not null primary key,
 employee_id domain_id not null,
 salary integer not null,
 bonus integer not null,
 totalsalary computed by (salary+bonus),
 constraint fk1 foreign key (employee_id) references employee(id));

(note : fk1 is the name of the constraint)

now we have a better database than our previous. We can see what is in our database by choosing Metadata - Extract Table. And the output is :

/* Extract Database D:\mydata\MYDATA.GDB */
CREATE DATABASE "D:\mydata\MYDATA.GDB" PAGE_SIZE 1024 
;

/* Domain definitions */
CREATE DOMAIN STRING30 AS VARCHAR(30);
CREATE DOMAIN STRING12 AS VARCHAR(12);
CREATE DOMAIN DOMAIN_ID AS INTEGER
  check(value>0);
CREATE DOMAIN DOMAIN_AGE AS INTEGER
  check(value>18 and value<55);>/* Grant permissions for this database */

Go to Previous Tutorial or Next Tutorial