ASSIGNMENT PHASE 1

Assignment Phase 1

Assignment Phase 1

Relationships between entities can be optional or compulsory. In our example, we could decide that a person is considered to be a customer only if they have bought a product. On the other hand, we could say that a customer is a person whom we know about and whom we hope might buy something—that is, we can have people listed as customers in our database who never buy a product. This example shows how to create a table with a primary key defined in full as a named primary key table constraint

CREATE TABLE demo_table (

id INTEGER NOT NULL,

txtdata VARCHAR(20),

CONSTRAINT demo_table_pk PRIMARY KEY (id)

)

;

This example shows how to create a table with a primary key defined using shorthand:

CREATE TABLE demo_table (

id INTEGER NOT NULL PRIMARY KEY,

txtdata VARCHAR(20),

)

;

Or shorter still:

CREATE TABLE demo_table (

id INTEGER PRIMARY KEY,

txtdata VARCHAR(20),

)

;

Creating tables with foreign key constraints

First the Primary key table must be defined before it can be referenced:

CREATE TABLE T1 (

Id INTEGER NOT NULL PRIMARY KEY,

Dt VARCHAR

)

;

Now the foreign key table can be created referencing the table above:

CREATE TABLE T2 (

Act INTEGER NOT NULL, -- will refer to Id in T1

Retr DATETIME,

Description VARCHAR,

CONSTRAINT FK1 FOREIGN KEY (Act) REFERENCES T1 (Id) -- the fk constraint

)

;

The statement above creates the foreign key constraint in separate line of the create table statement. This can be also be written in short form with the column definition it applies to:

CREATE TABLE T2 (

Act INTEGER NOT NULL REFERENCES T1 (Id), -- refer to Id in T1

Retr DATETIME,

Description VARCHAR

)

;

This CREATE TABLE statement was used for creating part of Demo database. This statement does not describe the columns to be used as foreign keys ...