#1
  1. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    5
    Rep Power
    0

    Newbie question...


    I come with a ton of Pick/multivalue database experience, but no SQL, so pardon what may sound dumb.

    I created a table with a primary key defined as an integer. I have several other tables the REFERENCE that key. I'm porting stuff over from an existing database where I'd like to enforce the rule that that key is a five digit integer. It's not necessary for mathematical purposes, just a standard.

    I can create references just fine, but when I import data into those other tables, I get errors because I'm importing null data into the field that references the original table - because the reference to that other table is not a required field. So I gather that null is not a valid integer. That's fine...

    I thought perhaps I could define my first table with an integer primary key and those other tables could just define the reference as character(5), but you guessed it, "datatype mismatch".

    So... is there a way to enforce my first table rule of a numeric primary key while allowing other tables that reference it to be optional (in other words, null if a relationship between that row and a row in the master table doesn't exist).

    I hope I have been clear.
    Thanks,
    Kevin
    TL;DR: Table1 has sequential integer key. Table2/Table3,etc optionally reference Table1, but since they don't always have to reference it, null entries are rejected.
  2. #2
  3. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Hi,

    a foreign key doesn't require the values to not be NULL in any way. If NULL gets rejected, that's because you've set up a NOT NULL constraint for the foreign key. If you did that by accident, remove it.

    A foreign key referencing an INT field cannot be a CHAR, of course. It's generally very unclean to store numbers as CHARs, because a CHAR can contain anything. So you lose all type safety.

    If you only want five digit numbers, use a CHECK constraint and simple math:

    Code:
    your_number_column INT REFERENCES ... CHECK(your_number_column BETWEEN 0 AND 99999)
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  4. #3
  5. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    5
    Rep Power
    0

    Not Null confusion...


    Thanks so far... I was trying to recreate tables, but it's tricky trying to simplify this as there are lots of inter-relations. But I believe my confusion comes because of "NOT NULL" designations. From what I understand "NOT NULL" is a default, and since it is a double negative, I think that throws me as well.

    I have a SHOW table containing Broadway type shows (practice database). I didn't get as far as the FOREIGN KEY problem (yet), when I ran into a (I believe) related problem. I have "show_opened" date field that is only occasionally populated:
    =======================================
    CREATE TABLE show
    (
    show_id integer NOT NULL,
    display_title character(50),
    sort_title character(50),
    artist_id integer,
    show_type character(4),
    show_opened date NULL,
    comments text,
    CONSTRAINT show_pkey PRIMARY KEY (show_id),
    CONSTRAINT show_artist_id_fkey FOREIGN KEY (artist_id)
    REFERENCES artist (artist_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT show_show_type_fkey FOREIGN KEY (show_type)
    REFERENCES show_type (show_type_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
    )
    WITH (
    OIDS=FALSE
    );

    =======================================
    As you can see, it is defined show_opened as "Null", yet I get an error while populating it:
    ERROR: invalid input syntax for type date: ""
    CONTEXT: COPY show, line 1, column show_opened: ""


    When I look at how pgAdminIII interpreted my CREATE TABLE command, it dropped my NULL instruction:

    CREATE TABLE show
    (
    show_id integer NOT NULL,
    display_title character(50),
    sort_title character(50),
    artist_id integer,
    show_type character(4),
    show_opened date,
    comments text,
    CONSTRAINT show_pkey PRIMARY KEY (show_id),
    CONSTRAINT show_artist_id_fkey FOREIGN KEY (artist_id)
    REFERENCES artist (artist_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION,
    CONSTRAINT show_show_type_fkey FOREIGN KEY (show_type)
    REFERENCES show_type (show_type_id) MATCH SIMPLE
    ON UPDATE NO ACTION ON DELETE NO ACTION
    )
    WITH (
    OIDS=FALSE
    );
  6. #4
  7. --
    Devshed Expert (3500 - 3999 posts)

    Join Date
    Jul 2012
    Posts
    3,957
    Rep Power
    1046
    Wait a second, I think there's a misunderstanding. The error message says nothing about NULL. It tells you that you're trying to insert an empty string into a date column. An empty string is not the same as NULL, they are two completely different values. And an empty string is no valid date, regardless of how you set up the database.

    So this is a problem of wrong input data. Where does the file come from?

    NULL is the default for columns. It's unnecessary to explicitly set it (and seems to be obsolete), so pgAdmin ignores it.
    The 6 worst sins of security ē How to (properly) access a MySQL database with PHP

    Why canít I use certain words like "drop" as part of my Security Question answers?
    There are certain words used by hackers to try to gain access to systems and manipulate data; therefore, the following words are restricted: "select," "delete," "update," "insert," "drop" and "null".
  8. #5
  9. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    5
    Rep Power
    0
    Originally Posted by Jacques1
    Wait a second, I think there's a misunderstanding. The error message says nothing about NULL. It tells you that you're trying to insert an empty string into a date column. An empty string is not the same as NULL, they are two completely different values. And an empty string is no valid date, regardless of how you set up the database.

    So this is a problem of wrong input data. Where does the file come from?

    NULL is the default for columns. It's unnecessary to explicitly set it (and seems to be obsolete), so pgAdmin ignores it.
    You're right, I didn't realize there's a difference between empty and NULL. This comes from a Pick database, which I've been very spoiled with for decades. There are no data types, for better or worse.

    As I mentioned before, the date field (for date show opened on Broadway) is only occasionally populated. Do I have to put a default date in there -or- just change it to a character() field and handle it at GUI input time?

    Thanks for your patience
  10. #6
  11. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    5
    Rep Power
    0
    I just realized - directly - where is the data coming from? I'm importing it in a tab delimited file via a COPY command. I can easily change what is in that tab delimited file.

    I'm reading up on the difference between NULL and "". If I discover that I should be sending something in place of "", I'm happy to do that.
  12. #7
  13. No Profile Picture
    Registered User
    Devshed Newbie (0 - 499 posts)

    Join Date
    Apr 2013
    Posts
    5
    Rep Power
    0

    Null Not Null


    After reading up on the COPY command, it seems that I need to take advantage of the NULL character replacement '\N' to send those null fields. That shouldn't be a problem.

    Am I on the right track?

IMN logo majestic logo threadwatch logo seochat tools logo