java2db

Sql PRIMARY KEY constraint on table column



Primary key constraint  identifies uniquely each row in the table.When you specify a primary key constraint to a table , the database engine enforces data uniqueness by creating  a unique index for the primary key columns.

PRIMARY KEY constraint  Restrictions :
PRIMARY KEY constraint  primary key column/columns allows unique values.
PRIMARY KEY constraint  It won’t allow NULL  values.
PRIMARY KEY constraint  Each table must have only one primary key.
PRIMARY KEY constraint  It can possible to define on multiple columns.
PRIMARY KEY constraint  None of the columns in the primary key can have data type LONG,LONG RAW,VARRAY,NESTED ….TABLE,OBJECT,LOB,BFILE, REF or TIMESTAMP WITH TIME ZONE.
PRIMARY KEY constraint  A composite primary key cannot have more than 32 columns.

 

PRIMARY KEY constraintPRIMARY KEY constraint creation :

Column level PRIMARY KEY constraint :

Syntax:

 CREATE TABLE table_name (column_one type CONSTRAINT  constraint_name PRIMARY KEY ,column_two type ,…..);

Example sql statement :

 

Table level PRIMARY KEY constraint :

Syntax:

CREATE TABLE table_name (column_one  type,column_two type,column_three type, CONSTRAINT constraint_name PRIMARY KEY(column to apply));

Example sql statement :

 

Composite key  constraint :

If more than one columns are having one primary key is called composite key.

Syntax:

CREATE TABLE table_name (column_one type,column_two type,column_three type, CONSTRAINT constraint_name PRIMARY KEY(more columns to apply));

Example sql statement :

 

 

PRIMARY KEY constraint PRIMARY KEY constraint using  ALTER TABLE :

For one column:

Syntax:

ALTER TABLE table_name  ADD  PRIMARY KEY(column name to apply);

 

Example sql statement :

 

For more than one columns:

Syntax:

ALTER TABLE table_name  ADD  CONSTRAINT constraint_name PRIMARY KEY(column_one ,column_two,….);

 

Example sql statement :

PRIMARY KEY constraint DROP a PRIMARY KEY constraint :

Syntax:

ALTER TABLE table_name DROP  PRIMARY KEY ;

 

Example sql statement:




Related Posts :
Sql FOREIGN KEY constraint on table column
Sql CHECK constraint on table column
Sql NOT NULL constraint on table column
Sql UNIQUE constraint on table column
Sql full outer join on tables with an example
Sql right outer join on tables with an example
Sql left outer join on tables with an example
Sql inner join on tables with an example
   [will not be published]



^ <