For those of you who know SQL

This is a discussion on For those of you who know SQL within the A Brief History of Cprogramming.com forums, part of the Community Boards category; I am fairly new to SQL and I have run into a problem. I am trying to create a table ...

  1. #1
    Cheesy Poofs! PJYelton's Avatar
    Join Date
    Sep 2002
    Location
    Boulder
    Posts
    1,728

    For those of you who know SQL

    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
    ('Multiple');

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

  2. #2
    UNBANNED OneStiffRod's Avatar
    Join Date
    Jan 2002
    Posts
    669
    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.
    My Avatar says: "Stay in School"

    Rocco is the Boy!
    "SHUT YOUR LIPS..."

  3. #3
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,139
    <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.
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

  4. #4
    Cheesy Poofs! PJYelton's Avatar
    Join Date
    Sep 2002
    Location
    Boulder
    Posts
    1,728
    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
    (
    id_number VARCHAR2(5) NOT NULL PRIMARY KEY,
    call_type VARCHAR2(1),
    score VARCHAR2(1)
    );

    ***** GUESS I WOULD NEED THE FUNCTION HERE ********

    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

  5. #5
    the hat of redundancy hat nvoigt's Avatar
    Join Date
    Aug 2001
    Location
    Hannover, Germany
    Posts
    3,139
    Code:
    CREATE OR REPLACE TRIGGER null_only_if_single_trigger BEFORE UPDATE OR INSERT ON phone_call
    FOR EACH ROW
    BEGIN
    IF :new.call_type <> 'Single' AND :new.score IS NULL THEN
    raise_application_error(-20512,'You cannot insert NULL scores for single calls');
    END IF;
    END;
    /
    Not tested, but it should work this way.
    hth
    -nv

    She was so Blonde, she spent 20 minutes looking at the orange juice can because it said "Concentrate."

    When in doubt, read the FAQ.
    Then ask a smart question.

  6. #6
    Cheesy Poofs! PJYelton's Avatar
    Join Date
    Sep 2002
    Location
    Boulder
    Posts
    1,728
    Thanks! I'll give it a try in class tonight.

  7. #7
    Seven years? civix's Avatar
    Join Date
    Jul 2002
    Posts
    605
    Okay.... Now that i've seen that...
    SQL is evil!
    Im gonna drop the F-BOMB now.

Popular pages Recent additions subscribe to a feed

Similar Threads

  1. OOP Question DB Access Wrapper Classes
    By digioz in forum C# Programming
    Replies: 2
    Last Post: 09-07-2008, 05:30 PM
  2. Please help create a loop for embedded SQL
    By cjohnman in forum C Programming
    Replies: 4
    Last Post: 04-24-2008, 07:46 AM
  3. Embedded SQL
    By sarac in forum C Programming
    Replies: 1
    Last Post: 05-04-2006, 10:09 AM
  4. Replies: 1
    Last Post: 03-21-2006, 07:52 AM
  5. Problem with embedded SQL in C/C++ (ECPG)
    By NeuralClone in forum C Programming
    Replies: 4
    Last Post: 10-21-2005, 06:16 PM

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21