Friday 12 February 2010

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

2 comments:

  1. Can't get passed a Statement failed, SQLCODE = -607. unsuccessful metadata update
    -object EMPLOYEE is in use

    Bryan

    ReplyDelete
  2. supplier of 3D printing, rapid prototyping, rapid manufacturing, plastic fabrication, tooling, CNC and injection molding services.

    ReplyDelete

These links are part of a pay per click advertising program called Infolinks. Infolinks is an In Text advertising service; they take my text and create links within it. If you hover your mouse over these double underlined links, you will see a small dialog box containing an advertisement related to the text. You can choose to move the mouse away and go on with your browsing, or to click on the box and visit the relevant ad. Click here to learn more about Infolinks Double Underline Link Ads.