Vfrdtyky

I'm trying to create an SQL database with the following fields:

connection= sqlite3.connect('Main Database')

crsr = connection.cursor()

#Creates a table for the teacher data if no table is found on the system

crsr.execute("""CREATE TABLE IF NOT EXISTS Teacher_Table(Teacher_ID INTEGER PRIMARY KEY,

                    TFirst_Name VARCHAR(25) NOT NULL,

                    TLast_Name VARCHAR (25) NOT NULL,

                    Gender CHAR(1) NOT NULL,

                    Home_Address VARCHAR (50) NOT NULL,

                    Contact_Number VARCHAR (14) NOT NULL);""")

connection.commit()

connection.close()

But when I input values, the gender field accepts more than one value
Database View

How can I make sure it only accepts one character for that field

How can I make sure it only accepts one character for that field

SQLite does not check the length constraints defined at type level, as is specified in the documentation on types:

(...) Note that numeric arguments in parentheses that following the type name (ex: "VARCHAR(255)") are ignored by SQLite - SQLite does not impose any length restrictions (other than the large global SQLITE_MAX_LENGTH limit) on the length of strings, BLOBs or numeric values.

So you can not enforce this at the database level. You will thus need to enforce this through your views, etc.

We can however, like @Ilja Everilä says, use a CHECK constraint:

CREATE TABLE IF NOT EXISTS Teacher_Table(

    Teacher_ID INTEGER PRIMARY KEY,

    TFirst_Name VARCHAR(25) NOT NULL,

    TLast_Name VARCHAR (25) NOT NULL,

    Gender CHAR(1) NOT NULL CHECK (length(Gender) < 2),

    Home_Address VARCHAR (50) NOT NULL,

    Contact_Number VARCHAR (14) NOT NULL

)