11

I am using Apache Derby database with ij 10.10.

I have two tables. First is usertable and the second is logintable. In my usertable I have two columns: userid and name. My logintable table has two columns: userid and password. I need to set one column in logintable as foreign key where the primary key is in the usertable.

I used the following command to create the table:

create table usertable (userid varchar(10) primary key, name varchar(20));

How do I write the logintable to set the userid as a foreign key referring to the above primary key?

Can anyone please help me out?

Abra
  • 19,142
  • 7
  • 29
  • 41
rainu
  • 733
  • 2
  • 12
  • 26

1 Answers1

17

I think you're looking for the FOREIGN KEY constraint syntax: http://db.apache.org/derby/docs/10.10/ref/rrefsqlj13590.html

And, more specifically, the REFERENCES syntax: http://db.apache.org/derby/docs/10.10/ref/rrefsqlj16357.html#rrefsqlj16357

So when you are creating the "logintable", at some point in the CREATE TABLE statement you will have something like:

 CONSTRAINT login_userid_ref FOREIGN KEY (userid) REFERENCES usertable(userid)

Note that the SQL language has various alternate syntax styles for declaring referential integrity constraints like these; for example you can use a simpler syntax that ends up being something like:

create table logintable(
    userid varchar(10) references usertable(userid),
    password varchar(20));
Bryan Pendleton
  • 16,128
  • 3
  • 32
  • 56
  • 2
    I used the following statment for creating the login table with foreign key `create table logintable(userid varchar(10) references usertable(userid),password varchar(20));` And this worked. Thank you for the hint. – rainu May 10 '14 at 06:25
  • @rainu - your table creation worked for me and I would recreate your comment as an answer – Brian Agnew Jan 11 '16 at 11:54
  • @rainu I edited your suggested syntax into the answer -- thanks! – Bryan Pendleton Feb 01 '16 at 23:41