Difference between revisions of "Constraints"
Jump to navigation
Jump to search
| Line 1: | Line 1: | ||
= Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL = | = Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL = | ||
''' NOT NULL has to be added at the column level, cannot be added at the table level''' | |||
''' If a primary key consist of more than one column (composite primary key) you must create it at the table level''' | |||
== Adding Constraints at table creation == | == Adding Constraints at table creation == | ||
CREATE TABLE a_animals( | CREATE TABLE a_animals( | ||
| Line 13: | Line 15: | ||
); | ); | ||
== Check Constraint on a specific Table == | == Check Constraint on a specific Table == | ||
SELECT constraint_name, constraint_type | SELECT constraint_name, constraint_type | ||
Revision as of 17:03, 13 October 2017
Constraints PRIMARY KEY, FOREIGN KEY, UNIQUE, CHECK, NOT NULL
NOT NULL has to be added at the column level, cannot be added at the table level If a primary key consist of more than one column (composite primary key) you must create it at the table level
Adding Constraints at table creation
CREATE TABLE a_animals( animal_id NUMBER(6), name VARCHAR2(25), license_tag_number NUMBER(10), admit_date DATE CONSTRAINT ail_admit_date_nn NOT NULL, adoption_id NUMBER(5), vaccination_date DATE CONSTRAINT ail_vaccination_date_nn NOT NULL, CONSTRAINT ail_animal_id_pk PRIMARY KEY(animal_id), CONSTRAINT ail_license_pk UNIQUE(license_tag_number) );
Check Constraint on a specific Table
SELECT constraint_name, constraint_type FROM user_constraints WHERE table_name = 'A_ANIMALS';
Add Constraints to Existing Table/column
ALTER TABLE customers ADD CONSTRAINT customers_cust_no_pk PRIMARY KEY(customer#);