View Full Version : For those of you who know SQL

01-29-2003, 06:38 PM
I am fairly new to SQL and I have run into a problem. I am trying to create a table that has among other things two columns, one named Call_Type and the other Score. Now when inserting in values, if Call_Type is "Single" then Score can be NULL, otherwise it cannot be NULL.

The problem is that I'm not sure how to code this. What I want is there to be an error if a user enters something like:

INSERT INTO myTable (Call_Type) VALUES

Since in this case score cannot be NULL. Anyone know how to code this?

01-29-2003, 10:48 PM
Here's some sample SQL commands

create database RealtyCenter;

use RealtyCenter;

create table Customers
customerID int unsigned not null auto_increment primary key,
contactID int unsigned not null

create table Orders
orderID int unsigned not null auto_increment primary key,
customerID int unsigned not null,
amount float(6,2),
date date not null,
order_status char(10),
shipFName char(20) not null,
shipLName char(20) not null,
shipAddr char(40) not null,
shipCity char(20) not null,
shipState char(20),
shipZip char(10),
shipCountry char(20) not null


sometimes the syntax is different like in mySQL requires parenthese instead of brackets and also accent marks around table/database names.

create table `Customers`
`customerID` int unsigned not null auto_increment primary key,
`contactID` int unsigned not null

create table `Orders`
`orderID` int unsigned not null auto_increment primary key,
`customerID` int unsigned not null,
`amount` float(6,2),
`date` date not null,
`orderStatus` char(10),
`shipFName` char(20) not null,
`shipLName` char(20) not null,
`shipAddr` char(40) not null,
`shipCity` char(20) not null,
`shipState` char(20),
`shipZip` char(10),
`shipCountry` char(20) not null

create table `Properties`
`propID` int unsigned not null auto_increment primary key,
`MLSID` char(40),

When creating the tables and columns you can only say whether that value can have or not have a NULL value - you need to do conditional processing either when u insert the data or it really depends on the database your using as to how it will allow u to process that.

01-30-2003, 01:33 AM
<Disclaimer: This is Oracle, maybe the functionality has another name under SQL Server. I'm not sure if it exists at all in mySQL>

When you create a table, you can create constraints like "can be null" or "may never be null". However, this cannot be conditionals dependent on table data. When you need data dependent checks, you use functions. In Oracle they are called Triggers, because these special functions get triggered on custom events. You want a check whenever a row of data is about to be changed and want the check to fail if the data of two columns does not match your criteria. Your Trigger therefore has to fire "BEFORE UPDATE OR INSERT" for you to check data before it gets into your table.
I suggest you get a good manual for your database, because the language you write the function in is different for most systems. If you happen to use Oracle, post it and I'll help you with the rest of the syntax.

01-30-2003, 09:53 AM
Whoops, I should have mentioned that I was using Oracle, sorry! I don't have my Oracle book with me at the moment so I can't look up functions, but this is a simplified version of what I got:

CREATE TABLE phone_call
call_type VARCHAR2(1),
score VARCHAR2(1)


INSERT INTO phone_call (id_number, call_type) VALUES
('12345','Single'); *** no error here

INSERT INTO phone_call (id_number, call_type) VALUES
('12346','Multiple'); *** should be an error since now score can't be null

01-30-2003, 12:53 PM
CREATE OR REPLACE TRIGGER null_only_if_single_trigger BEFORE UPDATE OR INSERT ON phone_call
IF :new.call_type <> 'Single' AND :new.score IS NULL THEN
raise_application_error(-20512,'You cannot insert NULL scores for single calls');

Not tested, but it should work this way.

01-30-2003, 01:15 PM
Thanks! I'll give it a try in class tonight.

01-30-2003, 06:55 PM
Okay.... Now that i've seen that...
SQL is evil!
Im gonna drop the F-BOMB now.