should i write FK, NULL or NOT_NULL?

Question

i'm trying to do a data dictionary and am filling the tables with ( attributes , Null, Type , length) one of the tables i got is like

create table user(
    user_number(8),
    First_name varchar(30) not null,
    Last_name varchar(30) not null,
    DOB date not null,
    nationality varchar (30) not null,
    gender char (1) not null,
    address varchar(30) not null,
    Primary key(user_ID)
    foreign key(profile_ID)
);

when i start describing them in the table it goes like

______________________________________________________
| column name  |     NULL   |    key  |     type     |
|====================================================|
| user_ID      |     null   |    pk   |     varchar  |
| profile_ID   |     null   |    fk   |     varchar  |
|______________|____________|_________|______________|

my question is , is it right to put both pk & fk as null? or should i make fk Not-Null?

Answer 1

I don't think you have a tight grasp on what a primary key, a foreign key, and the meaning of null versus not null is in a database. Or at least, if you do, you're not using correct syntax to describe what you're trying to do.

Most column definitions include a name, a length (sometimes), a data type, and qualifiers like NULL/NOT NULL, AUTOINCREMEMT, PRIMARY KEY, etc. In addition, after all of your column definitions, most database have specific syntactic constructs to define constraints, such as indexed columns, primary keys, unique columns, etc. Note that primary key columns have to be columns defined above in the column definitions.

Each database has nuances in their syntax, but most follow a pretty consistent pattern. For example, in MySQL, you might define a couple of tables as follows:

CREATE TABLE profiles (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    name VARCHAR(50) NOT NULL,
    -- other fields
);

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    profile_id INTEGER NOT NULL,
    first_name VARCHAR(30) NOT NULL,
    last_name VARCHAR(30) NOT NULL,
    date_of_birth DATETIME NOT NULL,
    nationality VARCHAR(30) NOT NULL,
    gender CHAR(1) NOT NULL,
    address VARCHAR(30) NOT NULL,
    CONSTRAINT FOREIGN KEY (profile_id)
        REFERENCES profiles (id)
        ON UPDATE CASCADE
        ON DELETE CASCADE );

Of course, the exact syntax you use will totally depend on what you're trying to do. In the example above, two tables will be created, profiles and users. In the users table, the id column is a primary key and the profile_id column is a foreign key that references the profiles table. Note how the foreign key (profile_id) is defined in the column definitions above; this is required. Also, almost all databases allow you to specify PRIMARY KEY as a qualifier in the column definitions. Optionally, you can specify it as a constraint like so:

CREATE TABLE users (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    -- other columns and constraints...
    PRIMARY KEY (id) );

The main reason you might want to use that syntax instead is if you have a multi-column primary key--two or more columns that aren't necessarily unique themselves, but taken together, make up a unique combination. But that's getting a bit advanced.

Most databases won't allow you to have a primary key column able to have null values in it. In MySQL, for example, if you don't declare the primary key column as NOT NULL explicitly, it will silently make it so anyway. If you were able to declare a primary key column as able to have a null value, only one of your records would be able to have null--multiple nulls would violate the primary key constraint.

Foreign keys can be null, but you probably don't want them to be null. That's kind of the point of a foreign key--to serve as a reference to another table's data, and you will almost always want all rows in the table with a foreign key to contain parent data in another table. About the only time you'd want a null foreign key is if you want a table to contained "orphaned" data. In the example above, if a user might not have a profile attached to his or her account, the profile_id column might be null.

I would highly suggest seeking out some basic tutorial information on primary and foreign keys. They're not hard concepts to master, but it something you will definitely need to know if you work with databases to any significant degree. I hope some of this helped, and you find what you're trying to do. If you have a concrete task you're trying to accomplish, it might be helpful to post what it is, as it's easier to work with an actual example than conceptually explain it hoping that something I've said is specifically applicable to what you're trying to do.