Re: Constraints question

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Wed, 22 Dec 1999 15:59:29 -0500
Message-ID: <4ne26skoitsst0t784rirjpd0tble2gnud@4ax.com>

A copy of this was sent to Mark Framness <framnesso_at_my-deja.com> (if that email address didn't require changing) On Wed, 22 Dec 1999 20:01:03 GMT, you wrote:

>Greetings All
>
>I am preparing for the SQL-PL/SQL OCP exam. One of the practice exams I
>have been working with has a question that I get right, but I don't know
>why it is right.
>
>The customer table is an existing table.
>CREATE TABLE sale
>(purchose_no NUMBER(9),
> customer_no NUMBER(9),
> CONSTRAINT sale_customer_id_fk REFERENCES customer(id),
> CONSTRAINT sale_purchase_no_pk PRIMARY KEY (purchase_no),
> CONSTRAINT sale_customer_no_nn NOT NULL (customer_no));
>
>The question is which line causes an error?
>

line 4 (and when you fix that, line 6)

tkyte_at_8i> create table customer(id number(9) primary key);

Table created.

tkyte_at_8i>
tkyte_at_8i> CREATE TABLE sale
  2 (purchose_no NUMBER(9),
  3 customer_no NUMBER(9),

  4     CONSTRAINT sale_customer_id_fk REFERENCES customer(id),
  5     CONSTRAINT sale_purchase_no_pk PRIMARY KEY (purchase_no),
  6     CONSTRAINT sale_customer_no_nn NOT NULL (customer_no));
   CONSTRAINT sale_customer_id_fk REFERENCES customer(id),
                                                     *

ERROR at line 4:
ORA-00907: missing right parenthesis

it could be:

tkyte_at_8i> CREATE TABLE sale
  2 (purchose_no NUMBER(9),
  3 customer_no NUMBER(9),
  4 CONSTRAINT sale_customer_id_fk foreign key(customer_no) REFERENCES customer(id),

  5     CONSTRAINT sale_purchase_no_pk PRIMARY KEY (purchase_no),
  6     CONSTRAINT sale_customer_no_nn NOT NULL (customer_no));
   CONSTRAINT sale_customer_no_nn NOT NULL (customer_no))
                                  *

ERROR at line 6:
ORA-00904: invalid column name

then you get the syntax error on line 6.

>I have always been answering the NOT NULL contraint definition and that
>is indeed the answer. My answer rested upon the assumption that columns

no it should not be.

>that serve as foreign keys MUST be nullable. Is that assumption
>correct?

no, not at all.

tkyte_at_8i> create table p ( x int primary key);

Table created.

tkyte_at_8i> create table c ( x int not null references p(x) );

Table created.

tkyte_at_8i> desc c

 Name                                     Null?    Type
 ---------------------------------------- -------- ----------------------------
 X                                        NOT NULL NUMBER(38)

>
>I know that a foreign key must be either null or filled with a value
>from the table.column that the column references. Defining a foreign
>key column not null means that all values in the column must be filled
>in and from its parent column.
>
>My colleagues tell me that it is possible to create a column that is a
>foreign key and is not null, is that correct?
>
>Thanks

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Wed Dec 22 1999 - 14:59:29 CST