tags:

views:

101

answers:

7

I am aware of Oracle's create table syntax

CREATE TABLE MyTable(
   id int primary key,
   ...
);

This will create a table called MyTable with an int primary key. So, nothing new here.

but I am having difficulties understanding the following query:

CREATE TABLE departament (
   cod_dept    INTEGER CONSTRAINT dept_key PRIMARY KEY,
   dept_name CHAR(15) NOT NULL,
   admission    DATE      NOT NULL,
   localization CHAR(20))

When I look up on Oracle's SQL Developer software on departement's table, I can see 4 columns: cod_dept, dept_name, admission and localization. On the constraints tab, I can also see dept_key, but I am confused as to what this might mean. What is dept_key purpose here?

Edit

Ok, seems it is a way to define the name of the constraint you're adding to the table. My next question is why don't you just call it the same name as the primary key column? From what I've seen it seems Oracle by default just creates a random name for the constraint!

Thanks

+1  A: 

dept_key is the name of the primary key constraint. That means cod_dept is the unique identifier for your table, the mechanism for identifying a row, and so it can only have one occurrence of any given value.

APC
+7  A: 

When you write id int primary key, Oracle will create a primary key constraint to ensure uniqueness of primary key values. All constraints have names, so in this case Oracle assigns an autogenerated name to this constraint. But you can set a name of this constraint explicitly using the CONSTRAINT syntax:

cod_dept    INTEGER CONSTRAINT dept_key PRIMARY KEY

This name may be used later to refer to the constraint, for example, to delete or modify it:

ALTER TABLE department DROP CONSTRAINT dept_key;

EDIT: Constraint names are unique across the schema, so Oracle can't just use the name of primary key column as a constraint name.

axtavt
Thanks. Edited the OP, as I still have 1 more question.
devoured elysium
@devoured: Edited.
axtavt
Constraint names are unique across a SCHEMA (or user), not a database.
Gary
+2  A: 

Primary keys can be explicitly be named. dept_key is just a name.

Detect
+1  A: 

That is the constraint you created representing the primary key.

A table is made up of:

  • Columns (where the data lives)
  • Indexes (indexed copies of the data used for faster searching)
  • Constraints (rules about what data can be in the table, including PK, FK, and check constraints).
Larry Lustig
+1  A: 

dept_key is the name of the constraint. You specified the name here : "INTEGER CONSTRAINT dept_key PRIMARY KEY," so it will create a constraint with the name dept_key.

tHeSiD
+1  A: 

Another syntax for the same would be to write the following after your CREATE TABLE instruction.

ALTER TABLE department
ADD CONSTRAINT dept_key PRIMARY KEY (cod_dept)

dept_key is then the name of the constraint you created to be the primary key for this table. In order for a database engine to know the primary key, and to index it for fastest results and so forth, it needs to create a known constraint that is indexed. Here, it is you who has given the name which is dept_key.

For you kind information, it is often seen to write PK_[table name] for primary keys constraints and FK_[current_table_name]_[foreign_table_name] for foreign keys constraints.

Hope this helps! =)

Will Marcouiller
A: 

I think whenever we create a Primary Key value then by default Oracle will crate constraint for it with the same name but it looks like that u are creating constraint with some other name. Thank You

Bharath