Improving object-to-data mapping

The Small Business Development Center client table was previously defined without the inclusion of a point of contact for the client. The initial instinct of the database team was to simply add contact_name and contact_email columns to the client table. However, you object to this plan due to your instincts regarding proper data organization. In the future, a contact might be referenced in multiple tables. In this exercise, you will define table structures for the client and contact information that better separates the client and contact objects.

Recall the previous definition of the client table:

CREATE TABLE client (
    id SERIAL PRIMARY KEY,
    name VARCHAR(50),
    site_url VARCHAR(50),
    num_employees SMALLINT,
    num_customers INTEGER
);

Este exercício faz parte do curso

Creating PostgreSQL Databases

Ver curso

Instruções do exercício

Create a contact table with columns id (a primary key), name (max length of 50), and email (max length of 50).
Alter the client table by adding a contact_id column as a foreign key.

Exercício interativo prático

Experimente este exercício completando este código de exemplo.

-- Create the contact table
___ ___ ___ (
  	-- Define the id primary key column
	___ SERIAL ___ ___,
  	-- Define the name column
  	___ ___(50) NOT NULL,
    -- Define the email column
  	___ VARCHAR(___) NOT NULL
);

-- Add contact_id to the client table
ALTER TABLE ___ ADD ___ INTEGER NOT NULL;

-- Add a FOREIGN KEY constraint to the client table
ALTER TABLE ___ ADD CONSTRAINT fk_c_id FOREIGN KEY (___) REFERENCES ___(id);

Editar e executar o código