Skip navigation

Adding them is easy

Say we have 3 tables, users, sports, and users_sports_hobbies. The user_sports_hobbies table relates the users and sports table to tell what sports, each user likes.


-- drop tables with REFERENCES to table
-- because you can't drop the users or sports
-- tables WHILE users_like_sportsx refers
-- to it!
-- drop table users_like_sports1;
-- drop table users_like_sports2;
-- drop table users_like_sports3;
-- drop table users;
-- drop table sports; 






-- create the users table
create table users(

  user_id int not null primary key identity,
  username varchar( 255 )

) ;


-- and the sports table
create table sports(

  sport_id int not null primary key identity,
  sport_name varchar( 255 )

) ;

-- create the users_like_sports1 table
-- with foreign keys
-- compact notation, not naming the foreign key
-- constraints
create table users_like_sports1 (

  rel_id int not null primary key identity,

  user_id_fk int not null
  FOREIGN KEY REFERENCES users( user_id ),

  sport_id_fk int not null
  FOREIGN KEY REFERENCES sports( sport_id )

) ;

-- now here's the same thing, a users_like_sports table
-- only using a different notation to declare the
-- foreign keys.  this is completely functionally
-- equivalent to the above way, only in this
-- way i'm showing you here, I am NAMING
-- the foreign key constraints.  This is useful
-- for DROPPING the foreign key constraint off
-- the table later.
create table users_like_sports2 (

  rel_id int not null primary key identity, -- this is the "relationship id",
  -- i am in the habit of ALWAYS having a primary integer key for
  -- every field, even if it doesn't obviously serve any purpose here
  -- It may come in useful later, and its a real ass-biter to NOT
  -- have put in a simple integer primary key  

  -- Here is one way to do it:
  user_id_fk int not null
  CONSTRAINT fk_1a FOREIGN KEY REFERENCES users( user_id ),
  -- now we've said that the user_id_fk field
  -- is constrained by a constraint named "fk_1"
  -- and its a foreign key constraint, referencing
  -- the user_id column from the users table.
  -- (the name of the constraint "fk_1" 
  -- is useful for deleting this constraint later, if need be)

  sport_id_fk int not null
  CONSTRAINT fk_2a FOREIGN KEY REFERENCES sports( sport_id )

) ;



-- Finally here is a different notation.  This is a 
-- more common blown out notation
-- listing the foreign key references
-- at the bottom of the table specification.
create table users_like_sports3 (

  rel_id int not null primary key identity,

  user_id_fk int not null,
  sport_id_fk int not null,

  CONSTRAINT fk_1b FOREIGN KEY (user_id_fk) REFERENCES users( user_id ),
  CONSTRAINT fk_2b FOREIGN KEY (sport_id_fk) REFERENCES sports( sport_id )

) ;


-- ***
-- DROPPING THE CONSTRAINTS
-- Now here is how you drop the foreign key constraints
alter table users_like_sports2
drop constraint fk_1a ;

alter table users_like_sports2
drop constraint fk_2a ;

-- users_like_sports2 table now has
-- NO CONSTRAINTS AT ALL.  They
-- have been DROPPED.


-- Dropping the constraints for
-- table users_like_sports3
alter table users_like_sports3
drop constraint fk_1b ;
alter table users_like_sports3
drop constraint fk_2b ;

-- we can't easily drop the
-- constraints for users_like_sports1
-- since we don't have the constraint
-- names.
drop table users_like_sports1 ;


-- now that all references and constraints
-- to these two tables have been dropped,
-- we can safely drop the users and sports tables.
drop table users;
drop table sports;


Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: