Creating a table using SQL In Access

Question

So I am trying to create this table in access SQl. I know the basics of creating tables. However I am stuck with extra details to each field. HEre is what I need to create:

enter image description here

How would i go about setting required to yes/no, the caption and primary key etc..?

Doesn't MS Access have a manual that explains the SQL statements? — , Oct 26 '14 at 08:20

score 0 · Accepted Answer · edited May 23 '17 at 10:33

Setting Yes/No

Setting yes/no is done by adding a NOT NULL constraint to the field definition:

CREATE TABLE IceCream (
    Flavor TEXT(25) NOT NULL
);

Setting Caption

There doesn't seem to be a way to set this using SQL. See MS Access: setting table column Caption or Description in DDL?.

Primary Key

The primary key can be set using the PRIMARY KEY clause on a single field, or as a separate CONSTRAINT definition. If you have a multi-field primary key, you must use the second form.

CREATE TABLE IceCream (
    Flavor TEXT(25) PRIMARY KEY
);

CREATE TABLE Sundae (
    Name   TEXT(25),
    Flavor TEXT(25),
    CONSTRAINT PK_Sundae
        PRIMARY KEY (Name)
);

Foreign Key

The foreign key can be set with a CONSTRAINT definition:

CREATE TABLE IceCream (
    Flavor TEXT(25) PRIMARY KEY
);

CREATE TABLE Sundae (
    Name   TEXT(25) PRIMARY KEY,
    Flavor TEXT(25),
    CONSTRAINT FK_Sundae_IceCream
        FOREIGN KEY (Flavor)
        REFERENCES IceCream (Flavor)
);

Disclaimer: These are not examples of proper database design (normalization and such). They're just examples I made up on the spot.

References

Creating a table using SQL In Access

1 Answers1