Adding multiple constraints in a single table

Prerequisite – SQL Constraints

We can create a table with more than one constraint in its columns. Following example shows how we can define different constraints on a table.

Adding constraints in Create command :

• Sr_no is a Primary Key.
• Branch_no is the foreign key referencing Branch table.
• Company type will hold either of the values : ‘1C’, ’2C’, ’3C’

Syntax :

Create table Fd_master(Sr_no varchar2(10), 
Branch_no varchar2(10), account_no varchar2(20),
company_type varchar2(20), constraint pk primary key(Sr_no),  
constraint fk foreign key (Branch_no) references Branch,
constraint chk check(company_type in (‘1C’, ’2C’, ’3C’)));

1. Primary Key constraint –
   Sr_no in Fd_master where pk is userdefined name given to Primary key.
2. Foreign Key constraint –
   Branch_no in Fd_master where fk is name of foreign key that references branch table.
3. Check constraint –
   company_type in Fd_master where chk is name that will check the given values i.e ‘1C’, ‘2C’, ‘3C’

Adding constraints in Alter command :
Multiple columns level constraints can be added via alter command. It can be added in parent-child table in a serial order.

Constraint 1 (Default) :
Create two tables –

• Parent having default constraint on ID with the default value 1. Name given to constraint is DF_ParentTable.
• Foreign table with ID column.

ID in both the table is made ‘NOT NULL’.

Syntax :

CREATE TABLE ParentTable (ID int not null constraint DF_ParentTable default (1), 
name varchar2(5));

CREATE TABLE ForeignTable (ID int not null, Col2 VARCHAR(5));

Constraint 2 (Check) :

ALTER TABLE ParentTable ADD CONSTRAINT CK_ParentTable_ID CHECK(ID<100);

Constraint 3 (Foreign Key) :
Foreign Key

ALTER TABLE ParentTable 
ADD CONSTRAINT FK_parent_foreign FOREIGN KEY (ID) REFERENCES ForeignTable(ID);

My Personal Notes arrow_drop_up

Save