Constraints

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#);

Create A Foreign Key Constraint

ALTER TABLE BOOK_STORES
ADD CONSTRAINT bk_str_rep_id_fk FOREIGN KEY(rep_id)
REFERENCES STORE_REPS(rep_id)

ADDING MULTI COLUMN CONSTRAINTS TO AN EXISTING TABLE

ALTER TABLE sales
ADD CONSTRAINT product_id_pk
PRIMARY KEY(id, name, product);

DROP A CONSTRAINT

ALTER TABLE tablename
DROP CONSTRAINT constraint_name_pk

Back To Top- Home - Category