What does keyword CONSTRAINT do in this CREATE TABLE statement.

Question

I'm learning how to use sqlite3 with python. The example in the text book I am following is a database where each Country record has a Region, Country, and Population.

The book says:

The following snippet uses the CONSTRAINT keyword to specify that no two entries in the table being created will ever have the same values for region and country:

>>> cur.execute('''
CREATE TABLE PopByCountry(
    Region TEXT NOT NULL,
    Country TEXT NOT NULL,
    Population INTEGER NOT NULL,
    CONSTRAINT Country_Key PRIMARY KEY (Region, Country))
''')

Please could you explain what CONSTRAINT Country_Key does here. If I remove it, the PRIMARY KEY statement alone seems to ensure that each country has a unique name for that region.

Answer 1

Country_key is simply giving a name to the constraint. If you do not do this the name will be generated for you. This is useful when there are several constraints on the table and you need to drop one of them.

As an example for dropping the constraint:

ALTER TABLE PopByCountry DROP CONSTRAINT Country_Key

Answer 2

If you omit CONSTRAINT Contry_Key from the statement, SQL server will generate a name for your PRIMARY KEY constraint for you (the PRIMARY KEY is a type of constraint).

By specifically putting CONSTRAINT in the query you are essentially specifying a name for your primary key constraint.