Re: Constraints question

Mark Framness <framnesso_at_my-deja.com> wrote in message news:83rals$4c2$1_at_nnrp1.deja.com...
> 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?

OK. I find this question in the Oracle Assessment Test. You make a typo, you put a comma in the end of line 3. NOT NULL is a column constraint, not a table constraint, so the syntax is not correct.

> I have always been answering the NOT NULL contraint definition and that
> is indeed the answer. My answer rested upon the assumption that columns
> that serve as foreign keys MUST be nullable. Is that assumption
> correct?

No, it's not correct.

> 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?

Yes. Received on Fri Dec 24 1999 - 09:20:51 CST