2

I am practicing database creation and tablespaces and table implementation. I have a problem which I have searched the internet for some ideas, but none of them are precise to what I have now. My question is if it is possible to create a table which uses a foreign key which references to another table column in a different tablespace. Oracle SQL.

create table agent(
code Number(4), 
name varchar(30), 
area Number(4), 
constraint pk_Code primary key (code), 
constraint fk_Area foreign key (area) references Warehouse(code)) 
tablespace sales;
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
ditmark12
  • 111
  • 1
  • 11
  • As you can suppose Warehouse is in a tablespace named Venues. It is all ficticious of course. Any help will be appreciated. Thanks in advance. – ditmark12 Jul 31 '14 at 05:00
  • 1
    What error are you getting when you run this statement? – Mureinik Jul 31 '14 at 05:12
  • Hello. It is not an error. I just want fk_Area to be referencing Warehouse's code, but the thing is that Warehouse is a table which was created in another tablespace. Table 'agent' is in people tablespace for instance. – ditmark12 Jul 31 '14 at 05:20
  • 1
    Why do you think this might be a problem? Why haven't you just tried it - you'd have seen the tablespaces are irrelevant? 'Practicing' implies you're actually doing things, after all. – Alex Poole Jul 31 '14 at 07:17
  • Yes. I was creating the script first and didn't think of just do it. I prefer to be sure before making such changes. It's just me. – ditmark12 Aug 01 '14 at 05:00

2 Answers2

3

In Oracle, tablespaces are logical containers of tables used to manage their storage parameters (most importantly, the way they map to data files).

Tablespaces have no effect on table's permissions or constraints. It's perfectly possible to add a foreign key to a table referencing a table on a different tablespace.

Mureinik
  • 297,002
  • 52
  • 306
  • 350
  • 1
    Surely a tablespace is a _physical_ container of data without logical constraints? If it were a logical container of data (like a schema) then permissions would need to be explicitly granted. – Ben Jul 31 '14 at 07:34
  • 1
    A tablespace is a *logical* representation of the *physical* space. You assign a table to a tablespace, and can then play around with the tablespace (e.g., extending files, changing allocation policies, etc.) without having to manage each table individually. – Mureinik Jul 31 '14 at 07:37
  • Thank you for confirming this. I appreciate your help. – ditmark12 Aug 01 '14 at 05:02
1

As @Mureinik said, it is perfectly possible to point foreign keys to tables in other tablespaces. Having said that, I would tend not to do that. Having a foreign key cross a tablespace border is some (albeit mild) indication that the tablespace setup could do with some reconsideration.

I would personally use tablespaces as an administrative unit, containing tables (and or indexes) that share some (usually physical) properties, which should be made readonly or backed up or transported together...

wolφi
  • 8,091
  • 2
  • 35
  • 64
  • I see. But, the thing here is that it was to recap all topics for class. Thank you for reaffirming this. – ditmark12 Aug 01 '14 at 05:04