Open In App

Adding multiple constraints in a single table

Improve
Improve
Like Article
Like
Save
Share
Report

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

Last Updated : 17 Dec, 2020
Like Article
Save Article
Previous
Next
Share your thoughts in the comments
Similar Reads