CODEWITHSUNDEEP
sqloracle-databaseoracle10g
Java Beginner
Java Beginner

Reputation: 1655

What is the Difference between adding Column as Foreign Key and as a Constraint

I am Using Oracle 10g. I am Adding new column deptId to my UserList Table where I use deptId column as Foreign key which references other table Column Departments.DepartmentId

Is there Difference between adding foreign key as constraint and First Query

Query1

ALTER TABLE UserList
ADD FOREIGN KEY (DeptId)
REFERENCES Departments(DepartmentId)

Query2

ALTER TABLE UserList
ADD CONSTRAINT  fk_DeptId FOREIGN KEY (DeptId)
REFERENCES Departments(DepartmentId)

Upvotes: 4

Views: 703

Answers (3)

Ahmed
Ahmed

Reputation: 450


ALTER TABLE [dbo].[UserList]  WITH NOCHECK ADD  CONSTRAINT [fk_DeptId] FOREIGN KEY([DeptId])
REFERENCES [dbo].[Departments] ([DepartmentId])

No, there is no difference in both of your queries.But you to name the foreign key constraints names . You can use the above query.

Upvotes: -2

David Aldridge
David Aldridge

Reputation: 52336

There is no difference except in your use of the optional "CONSTRAINT" and constraint name clause.

There are two kinds of constraint definition: inline and out of line. The former operates on a column as part of the column definition, and hence does not need to name the DeptID column. The latter is part of the table definition and therefore does.

Both of your examples are out of line constraints, but you have not named the constraint in the former case, which is a bad practice:

http://docs.oracle.com/cd/E18283_01/server.112/e17118/clauses002.htm#g1053592

Upvotes: 4

Eric Citaire
Eric Citaire

Reputation: 4513

The second syntax allows you to name your constraint. The first doesn't.

Upvotes: 4

Related Questions