LinuxQuestions.org
Share your knowledge at the LQ Wiki.
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Software
User Name
Password
Linux - Software This forum is for Software issues.
Having a problem installing a new program? Want to know which application is best for the job? Post your question in this forum.

Notices


Reply
  Search this Thread
Old 03-23-2006, 05:26 AM   #1
prabhatsoni
Member
 
Registered: Oct 2004
Location: India
Distribution: FC 12
Posts: 233

Rep: Reputation: 30
mysql - data validation at the table-creation-time


Hello friends,

I have a table of users (user_id,password,level,..).
Now I want to create another table (station,in-charge,category,....). I want that the field in-charge of the second table should be looked up from the user_id field of the users table. This restriction I want to impose at the time of creation of table, so that always the in-charge is someone having a valid user_id.

Can anyone help me in this.


Thanks in advance,


Prabhat Soni
 
Old 03-23-2006, 06:32 AM   #2
zeitounator
Member
 
Registered: Aug 2003
Location: Montpellier, France, Europe, World, Solar System
Distribution: Debian Sarge, Fedora core 5 (i386 and x86_64)
Posts: 262

Rep: Reputation: 30
You need a foreign key constraint. This will work only with InnoDB tables. You can have a look at the doc for in depth explanations, more specifically http://dev.mysql.com/doc/refman/5.0/...nstraints.html

Very quickly to give you an idea:
Code:
create table tb1 (
 id int not null auto_increment,
 val varchar(255) not null,
 primary key (id)
) engine=innodb;

create table tb2 (
  id int not null auto_increment,
  refFromTb1 int not null,
  val varchar(255),
  foreign key (refFromTb1) references tb1(id) on delete cascade,
  primary key (id)
) engine=innodb;
 
Old 03-24-2006, 01:13 AM   #3
prabhatsoni
Member
 
Registered: Oct 2004
Location: India
Distribution: FC 12
Posts: 233

Original Poster
Rep: Reputation: 30
Thanks zeitounator,
You have been of immense help.

Prabhat Soni
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
Problem inserting data into a mysql table using PHP Rockgod2099 Programming 13 08-03-2005 01:27 AM
why do i get this error when inserting data to a mysql table verbatim Programming 2 06-15-2005 07:12 AM
mysql - transfer data from one table to another?? macinslaw Programming 22 08-20-2004 02:29 PM
Mysql table creation problems!! linux_child001 Linux - Newbie 10 06-28-2004 02:10 PM
retriving mysql data, and placing it inside a table in html using perl rhuser Programming 1 03-12-2003 06:04 AM

LinuxQuestions.org > Forums > Linux Forums > Linux - Software

All times are GMT -5. The time now is 07:14 AM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Open Source Consulting | Domain Registration