I have to create a table in SQL(I am using IntelliJ and Java), and the table must respect the following restrictions.
The table will contain the following columns:
In addition to that, we have the following restrictions:
the table will only be created if the table does not exist already
the primary key will be the title
2 newsletters can't have the same description
the news_description can not be null
in case the publication_date is not specified I need to set it to 27 April 2019 21:11:12 using UTC and the date must be in UNIX format
CREATE TABLE IF NOT EXISTS posts (
title VARCHAR,
news_description TEXT UNIQUE NOT NULL,
publication_date "I don't know what I should write here in order to respect the last restriction"
PRIMARY KEY(title)
);
So my question is how can I respect all of the restriction I have said above all while I create the table
NOTE: This is homework I have and I can't add anything besides the table there, so I need to respect all of the restrictions within the creation of the table.
The data type of publication_date
must be INTEGER
and the default value for this column must be 1556399472
which is the equivalent timestamp for 27 April 2019 21:11:12
.
There is no problem to store dates as integers in SQLite because it is easy to retrieve a readable date by using the 'unixepoch'
modifier with the Date And Time Functions of SQLite.
For example:
SELECT DATETIME(1556399472, 'unixepoch')
returns:
2019-04-27 21:11:12
Also there is no data type VARCHAR
in SQLite. You should use TEXT
.
So the create statement of your table can be:
CREATE TABLE IF NOT EXISTS posts (
title TEXT,
news_description TEXT UNIQUE NOT NULL,
publication_date INTEGER NOT NULL DEFAULT 1556399472,
PRIMARY KEY(title)
);
You may remove NOT NULL
from the definition of publication_date
if you want it to be nullable.