CHAPTER 28 FROM DATABASES TO DATATYPES Creating (Graphic web design)
CHAPTER 28 FROM DATABASES TO DATATYPES Creating an automatically incrementing primary key: CREATE TABLE staff ( staffid SERIAL NOT NULL PRIMARY KEY, fname TEXT NOT NULL, lname TEXT NOT NULL, email TEXT NOT NULL ); Creating a single-field primary key: CREATE TABLE citizen ( ssid VARCHAR(9) NOT NULL PRIMARY KEY, fname TEXT NOT NULL, lname TEXT NOT NULL, zipcode VARCHAR(10) NOT NULL ); Creating a multiple-field primary key: CREATE TABLE friend ( fname TEXT NOT NULL, lname TEXT NOT NULL, nickname TEXT NOT NULL, PRIMARY KEY(lname, nickname) ); REFERENCES The REFERENCES attribute specifies that the values in a column (or group of columns) must match the values appearing in some row of another table. This is done to ensure referential integrity between the two tables. As an example, we could rewrite the staff table in our previous example to the following: CREATE TABLE staff ( staffid SERIAL NOT NULL PRIMARY KEY, ssid VARCHAR(9) REFERENCES citizen (ssid), email TEXT NOT NULL ); Created this way, it is now impossible to add an entry to the staff table that does not have a corresponding entry in the citizen table. While some would say this approach to staffing might be short-sighted in today s global economy, opponents of illegal immigration would surely applaud this design. This relationship between the two tables is often referred to as a foreign key (no pun intended), and it provides other benefits as well. You ll notice that we eliminated the fnameand lname columns from our table; we did this because we can now infer this information from the relationship between the two tables. This also means that, should someone s name change (for example, when someone gets married), we do not have to write extra application code to propagate the changes throughout our database: the change can be made in one place and all
We highly recommend you visit web and email hosting services if you need stable and cheap web hosting platform for your web applications.