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 Off