List:General Discussion« Previous MessageNext Message »
From:Mechain Marc Date:November 26 2004 1:25pm
Subject:RE: How to retrieve constraints and links from MySQL tables?
View as plain text  
You can use:

show create table employee;

or 

show table status like 'employee';
in the column "comment" you have the information you are looking for.

Marc.

-----Message d'origine-----
De : Varakorn Ungvichian [mailto:rover_wow@stripped]
Envoyé : vendredi 26 novembre 2004 09:32
À : mysql@stripped
Objet : How to retrieve constraints and links from MySQL tables?


So, I'm running a MySQL database (version: 4.0.21) for
a personal project of mine, and I would like to learn
how to retrieve constraints and links from the table
after it's been created. The create statements read
thusly:

CREATE TABLE positions (  
position_id tinyint(4) DEFAULT '0' NOT NULL
AUTO_INCREMENT, 
position_name varchar(20) NOT NULL, 
position_salary float  NOT NULL,
PRIMARY KEY (position_id),  
UNIQUE position_id (position_id)
);

CREATE TABLE employees (  
employee_id tinyint(4) DEFAULT '0' NOT NULL
AUTO_INCREMENT,  
employee_first varchar(20) NOT NULL,
employee_last varchar(20) NOT NULL,  
employee_address varchar(255) NOT NULL,
position_id tinyint(4) NOT NULL default 1,
employee_start date,
employee_temp bool default 0,
FOREIGN KEY (position_id) references
positions(position_id), 
PRIMARY KEY (employee_id),  
UNIQUE employee_id (employee_id)
);

When I run "show columns from employees", there is no
indication that the "position_id" field in "employees"
is linked to that of "positions". This is the
resulting table:

+------------------+--------------+------+-----+---------+----------------+
| Field            | Type         | Null | Key |
Default | Extra          |
+------------------+--------------+------+-----+---------+----------------+
| employee_id      | tinyint(4)   |      | PRI | NULL 
  | auto_increment |
| employee_first   | varchar(20)  |      |     |      
  |                |
| employee_last    | varchar(20)  |      |     |      
  |                |
| employee_address | varchar(255) |      |     |      
  |                |
| position_id      | tinyint(4)   |      |     | 1    
  |                |
| employee_start   | date         | YES  |     | NULL 
  |                |
| employee_temp    | tinyint(1)   | YES  |     | 0    
  |                |
+------------------+--------------+------+-----+---------+----------------+

Is there a command or something that will display what
constraints (or links) exist in a given table?

Varakorn Ungvichian


		
__________________________________ 
Do you Yahoo!? 
Yahoo! Mail - Helps protect you from nasty viruses. 
http://promotions.yahoo.com/new_mail

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=1

Thread
How to retrieve constraints and links from MySQL tables?Varakorn Ungvichian26 Nov
  • Re: How to retrieve constraints and links from MySQL tables?Martijn Tonies26 Nov
    • Re: How to retrieve constraints and links from MySQL tables?Varakorn Ungvichian28 Nov
    • Re: How to retrieve constraints and links from MySQL tables?Varakorn Ungvichian28 Nov
      • Re: How to retrieve constraints and links from MySQL tables?Martijn Tonies28 Nov
      • Re: How to retrieve constraints and links from MySQL tables?Michael Stassen28 Nov
        • Re: How to retrieve constraints and links from MySQL tables?Varakorn Ungvichian29 Nov
          • Re: How to retrieve constraints and links from MySQL tables?Michael Stassen29 Nov
RE: How to retrieve constraints and links from MySQL tables?Mechain Marc26 Nov
  • Re: How to retrieve constraints and links from MySQL tables?Martijn Tonies26 Nov