May 11th, 2009

How do Constraints Work?, FOREIGN KEY (Part 4 of 5)

Posted by admin in P. Table Creation

The format is:

          [CONSTRAINT  column_constraint_name]
            REFERENCES parent_table[(parent_columns)]

or

          [CONSTRAINT    table_constraint_name]
            FOREIGN KEY  (key_columns)
              REFERENCES parent_table[(parent_columns)]

You can omit the parent_column if the parent_column is the primary key of the parent_table. However, this is not recommended.
The foreign key can be defined as a column constraint:

          CREATE TABLE     PAYROLL_TBL (
                 EMPL_ID   CHAR(4) NOT NULL
                           REFERENCES EMPLOYEE_TBL(EMPL_ID),
                 ...       );

The foreign key can be defined as a named table constraint:

          CREATE TABLE PAYROLL_TBL (
                 EMPL_ID        CHAR(4) NOT NULL,
                 ...
                 CONSTRAINT     PAYROLL_FK1
                    FOREIGN KEY (EMPL_ID)
                     REFERENCES EMPLOYEE_TBL(EMPL_ID),
                 ...            );

                                                            or

          CREATE TABLE SAMPLE_TBL (
                 SAMPLE1_ID     CHAR(4) NOT NULL,
                 SAMPLE2_ID     CHAR(4) NOT NULL,
                 ...
                 CONSTRAINT     SAMPLE_FK
                    FOREIGN KEY (SAMPLE1_ID, SAMPLE2_ID)
                     REFERENCES PARENT_TBL(PARENT1_ID, PARENT2_ID),
                 ...            );

Comments are closed.

Sorry, the comment form is closed at this time.