Home » SQL & PL/SQL » SQL & PL/SQL » Insert compound foreign key after table creation (Oracle, 10g express, winxp)
Insert compound foreign key after table creation [message #347856] Sun, 14 September 2008 12:15 Go to next message
juanp.contreras
Messages: 8
Registered: September 2008
Junior Member
Embarassed

Hi...

suppose I have the following schemas and also that I have created them with their foreign key constraints:

TableA(p,q,r)

where (p,r)is a primary key...

TableB(x,y)

where x is a primary key...

I want to associate one or more rows of TableB with ONLY ONE row of TableA.

So... I did this:

ALTER TABLE TableB --this is the child table
ADD CONSTRAINT FK_TEST
FOREIGN KEY (x)
REFERENCES TableA(p,r)

I think that the problem is caused because both tables do not contain at least one attribute.... Also, I have seen the Oracle's syntax reference and I've noticed (perhaps erroneously Embarassed ) that the syntax doesn't support that...

Thanks


Re: Insert compound foreign key after table creation [message #347857 is a reply to message #347856] Sun, 14 September 2008 12:27 Go to previous messageGo to next message
BlackSwan
Messages: 25033
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & FOLLOW the Posting Guidelines as stated in URL above

Re: Insert compound foreign key after table creation [message #347858 is a reply to message #347856] Sun, 14 September 2008 12:33 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Use SQL*Plus and copy and paste your session: create table and all other statements up to the error.

Before please read OraFAQ Forum Guide, especially "How to format your post?" section.
Use the "Preview Message" button to verify.

Regards
Michel
icon14.gif  Re: Insert compound foreign key after table creation [message #347861 is a reply to message #347858] Sun, 14 September 2008 13:02 Go to previous messageGo to next message
juanp.contreras
Messages: 8
Registered: September 2008
Junior Member
Thanks for your interest...

Look:

CREATE TABLE CLIENTE(
"NOMBRE" VARCHAR2(30 BYTE) NOT NULL ENABLE,
"GENERO" VARCHAR2(4 BYTE),
"FECHANACIMIENTO" DATE NOT NULL ENABLE,
CONSTRAINT "CLIENTE_PK" PRIMARY KEY ("NOMBRE", "FECHANACIMIENTO"));

CREATE TABLE PLAN(
NUMCONTRATO NUMBER NOT NULL,
FECHAINICIO DATE,
FECHAFIN DATE,
PRECIO NUMBER,
CONENTRENADORPERSONAL CHAR(2 CHAR),
CONSTRAINT PLAN_PK PRIMARY KEY (NUMCONTRATO) ENABLE
)

The error:

ORA-02256: Number of referencing columns must mach the number of referenced columns

Yes... I know that Oracle is saying that the columns should be the same (and also the datatypes...). I now understand that it has to be like Oracle says because the joins and every else relational operator has to be able to work in the table....

I want to know where I have to put a new (common) attribute taking into account that I want a client from CLIENTE to has many (or at least one...) plans in Plan

thanks



Re: Insert compound foreign key after table creation [message #347863 is a reply to message #347861] Sun, 14 September 2008 13:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Too bad you didn't post the wrong statement and also you didn't choose to format your post and copy and paste your SQL*PLus session as requested.

Your design is wrong if you have a dependency with only one column. You must have a table for client and a table for client and "FECHANACIMIENTO" (whatever its meaning is, it would be better if you translated the column names).

Regards
Michel
Re: Insert compound foreign key after table creation [message #347866 is a reply to message #347863] Sun, 14 September 2008 13:28 Go to previous messageGo to next message
juanp.contreras
Messages: 8
Registered: September 2008
Junior Member
Excuse me... my sqlplus doesnt let me to cut and paste test so I had to write everything...

Ok... let then me ask only for the table design:

suppose that I have the following tables:

TableA(p,q,r) with (p,q) as the primary key
TableB(x,y) with x s the primary key

The semantic relationship that I want to stablish in the tables is:

"For every row of TableA, there is one or many rows of TableB related to it"

As I said in a post before, im thinking about adding some common attribute to one (or both) tables

thanks for your patience...

Re: Insert compound foreign key after table creation [message #347868 is a reply to message #347866] Sun, 14 September 2008 13:34 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You still don't post the statement that returns you an error.
What you way is NOT what you post with your statements.

My patience start to see its end coming.

Regards
Michel
Re: Insert compound foreign key after table creation [message #347870 is a reply to message #347868] Sun, 14 September 2008 14:00 Go to previous messageGo to next message
juanp.contreras
Messages: 8
Registered: September 2008
Junior Member
Thats all then... thanks for your patience and have a good day.
Re: Insert compound foreign key after table creation [message #347873 is a reply to message #347870] Sun, 14 September 2008 14:26 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
So you no more need an answer?

Regards
Michel
Previous Topic: Getting completed status of concurrent request
Next Topic: need hierarchical list from root node to leaf node; reading left to right
Goto Forum:
  


Current Time: Sat Dec 03 11:57:56 CST 2016

Total time taken to generate the page: 0.11362 seconds