On the move:

I'm currently moving the following to their own blogs: Unix, HTML for Blogs, Perl & Java.
The links are on the left side.

Friday, May 30, 2008

Constraints - Nulls? Table/Column?

  • A Foreign Key is called a Referential Integrity Constraint
  • A Foreign Key can't be in a remote database.
  • A Foreign Key can refer to a unique or primary key, even in the same table.
  • A check can't use the pseudocolumns.
  • Use the table constraint form to check more than one column.

set echo off
set feedback off
Drop Table T1 Cascade Constraints;
Drop Table T2 Cascade Constraints;
Drop Table T3 Cascade Constraints;
clear screen
set echo on
set feedback on
Create Table T3
( C3 Char(6)
, C6 Number(7,2) Constraint T3_Uk Unique
, C7 Date
, Constraint T3_Pk Primary Key (C3)
Create Table T2
( C2 Varchar2(4) Constraint T2_Pk Primary Key
, C3 Char(6) Constraint T2_Fk References T3(C3)
, C4 Date Constraint T2_C4_NN Not Null
, C5 Varchar2(10) Check (C5 <=9)
Create Table T1
( C1 Number(4) Not Null
, C2 Varchar2(4)
, Constraint T1_Pk Primary Key (C1,C2)
, Constraint T1_Fk Foreign Key (C2) References T2(C2)
Clear Screen
Ttitle Left 'Constraint_Type: ' xConstraint_Type ' Owner: ' Xowner ' R_Owner: ' Xr_Owner ' Index_Owner: ' Xindex_Owner Skip 2
Break On Constraint_Type Skip Page Dup

Column Owner Noprint New_Value Xowner
Column R_Owner Noprint New_Value Xr_Owner
Column Index_Owner Noprint New_Value Xindex_Owner
Column Constraint_Type Noprint New_Value Xconstraint_Type
Column Index_Name Format A5
Column Table_Name Format A5
Column Validated Format A9

Select *
From User_Constraints
Where Table_Name Like 'T_'
Order By Constraint_Type;

No comments:

Post a Comment