PostgreSQL Primary Key -1
- The PostgreSQL PRIMARY KEY is a column in a table which must contain a unique value which can be used to identify each and every row of a table uniquely. So it can be said that the PRIMARY KEY of a table is a combination of NOT NULL and UNIQUE constraint.
- The function of PRIMARY KEY is same as UNIQUE constraint but the difference is one table can contain only one PRIMARY KEY though the table can contain one or more NOT NULL and UNIQUE constraints.
- A PRIMARY KEY can be represented for one column or combination of columns.
- No NULL value can not be accepted in PRIMARY KEY.
- You can define one column as PRIMARY KEY by using column-level constraint. In case the primary key contains multiple columns, you must use the table-level constraint.
SYNTAX:
(
column1 datatype [ NULL | NOT NULL ],
column2 datatype [ NULL | NOT NULL ],
...
CONSTRAINT constraint_name
PRIMARY KEY (index_col1, index_col2, ... index_col_n)
);
(OR)
CREATE TABLE table_name
(
column1 datatype CONSTRAINT constraint_name PRIMARY KEY,
column2 datatype [ NULL | NOT NULL ],
...
);
PRACTICAL 1.primary constraint creation:
Below two examples is same. From the first example shows the table "postgres1" have created with constraints of NOT NULL and UNIQUE on "order_no" column.the second example are same and shows the PRIMARY KEY have created on "order_no" column and by default "PRIMARY KEY, btree" constraint have been set.
postgres=# CREATE TABLE POSTGRES1(
order_no integer NOT NULL UNIQUE,
DB_name character(35),
DB_grade character(1)
);
postgres=# \d postgres1
Table "public.postgres1"
Column | Type | Modifiers
----------+---------------+-----------
order_no | integer | not null
db_name | character(35) |
db_grade | character(1) |
Indexes:
"postgres1_order_no_key" UNIQUE CONSTRAINT, btree (order_no)
(OR)postgres=# CREATE TABLE POSTGRES2(
order_no integer PRIMARY KEY,
DB_name character(35),
DB_grade character(1)
);
postgres=# \d postgres2
Table "public.postgres2"
Column | Type | Modifiers
----------+---------------+-----------
order_no | integer | not null
db_name | character(35) |
db_grade | character(1) |
Indexes:
"postgres2_pkey" PRIMARY KEY, btree (order_no)
Note:"postgres1_order_no_key" and "postgres2_pkey" are the system generated unique and primary key namePRACTICAL 2.PostgreSQL PRIMARY KEY constraint with constraint name:
The below example shows, the table "POSTGRES3" have created with one PRIMARY KEY as named "pkey".
postgres=# CREATE TABLE POSTGRES3(
order_no integer CONSTRAINT PKEY PRIMARY KEY,
DB_name character(35),
DB_grade character(1)
);
postgres=# \d postgres3
Table "public.postgres3"
Column | Type | Modifiers
----------+---------------+-----------
order_no | integer | not null
db_name | character(35) |
db_grade | character(1) |
Indexes:
"pkey" PRIMARY KEY, btree (order_no)
.
Comments
Post a Comment