Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Multiple constraints on single column

Tags:

sql

oracle10g

Can we add multiple constraints on a single column?

like-

create table x(x varchar2(20), y number(2) not null,
constraint fk_cons foreign key(x) references user_info(user_id),
constraint null_cons not null(x)
)

this query is returning error ora-00904: invalid identifier....

like image 730
Anu Avatar asked Mar 16 '13 06:03

Anu


1 Answers

You have wrong syntax when create null_cons constraint:

Use this (table level check constraint):

CREATE TABLE x(
    x VARCHAR2(20), 
    y NUMBER(2) NOT NULL,
    CONSTRAINT fk_cons FOREIGN KEY(x) REFERENCES user_info(user_id),
    CONSTRAINT null_cons CHECK(x IS NOT NULL)
)

Or (use NOT NULL constraint on column):

CREATE TABLE x(
    x VARCHAR2(20) NOT NULL, 
    y NUMBER(2) NOT NULL,
    CONSTRAINT fk_cons FOREIGN KEY(x) REFERENCES user_info(user_id)
)

Or (Use column level check constraint):

CREATE TABLE x(
    x VARCHAR2(20) CHECK (X IS NOT NULL), 
    y NUMBER(2) NOT NULL,
   CONSTRAINT fk_cons FOREIGN KEY(x) REFERENCES user_info(user_id)
)
like image 73
Iswanto San Avatar answered Nov 15 '22 05:11

Iswanto San