| Author |
 Topic  |
|
|
Drucey
Starting Member
United Kingdom
5 Posts |
 Posted - 02/17/2011 : 08:00:43
|
Hi everyone, new to SQL and working on an assignment,
One little question - doesn't interfere with my studies:
I am creating tables one attribute is gender, in the CONSTRAINTS i would like a way to make sure it is either m or f without the use of another table just sql. What is the method of doing this (I also need a way to make a field either AM or PM but it would be done the same way)
Thanks for your time
|
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
|
|
Drucey
Starting Member
United Kingdom
5 Posts |
Posted - 02/17/2011 : 09:18:33
|
Thanx for the reply, i will definitely look into that.
Sorry to keep being a pain but i have the script to create said tables and after hours i really seem stuck. I will post one example which frustrates me:
create table Booking
( FOREIGN KEY (Groupid) REFERENCES Teams(Groupid),
FOREIGN KEY (chalet_no) REFERENCES Accomodation(Chalet_no),
CONSTRAINT bookingid_pk PRIMARY KEY (Groupid, Chalet_no));
generates the error:
*
ERROR at line 2:
ORA-00904: : invalid identifier |
 |
|
|
khtan
In (Som, Ni, Yak)
Singapore
16746 Posts |
Posted - 02/17/2011 : 09:33:13
|
quote:
ERROR at line 2:
ORA-00904: : invalid identifier
Looks like you are not using SQL Server. Maybe you should then we can help you with your script 
For questions on Oracle, try dbforums.com. SQLTeam is on Microsoft SQL Server
KH
Time is always against us
|
|
|
|
MIK_2008
Aged Yak Warrior
Pakistan
826 Posts |
Posted - 02/17/2011 : 10:16:53
|
quote:
Originally posted by Drucey
create table Booking
( FOREIGN KEY (Groupid) REFERENCES Teams(Groupid),
FOREIGN KEY (chalet_no) REFERENCES Accomodation(Chalet_no),
CONSTRAINT bookingid_pk PRIMARY KEY (Groupid, Chalet_no));
Perhaps you are not even using SQL I never saw a create table statement in that fashion  Dude google the "Oracle Create Table" you will get a good help |
Edited by - MIK_2008 on 02/17/2011 10:17:58 |
|
|
|
Drucey
Starting Member
United Kingdom
5 Posts |
Posted - 02/17/2011 : 11:42:43
|
Ah sorry, so new to this. I definitely am using oracle though, it creates tables fine just flags up a few errors - Thats actually how we were taught to create tables. Sorry to waste your time though.
Thanks again
|
|
|
|
Drucey
Starting Member
United Kingdom
5 Posts |
Posted - 02/17/2011 : 11:45:05
|
| And that one is a bit different, its not a normal table but a result of a many to many relationship being split, hence the foreign keys etc |
|
|
|
chris_n_osborne
Starting Member
USA
34 Posts |
Posted - 02/17/2011 : 21:50:56
|
quote:
Originally posted by Drucey
create table Booking
( FOREIGN KEY (Groupid) REFERENCES Teams(Groupid),
FOREIGN KEY (chalet_no) REFERENCES Accomodation(Chalet_no),
CONSTRAINT bookingid_pk PRIMARY KEY (Groupid, Chalet_no));
I do not think this will work. You must define at least one column in a CREATE TABLE statement.
To add CONSTRAINTS to an existing table, you use the ALTER TABLE statement:
CREATE TABLE Teams
(Groupid INTEGER NOT NULL
)
go
CREATE TABLE Accommodation2 -- I had an Accommodation table in my db already.
(Chalet_no INTEGER NOT NULL
)
go
CREATE TABLE Booking
(Groupid INTEGER NOT NULL
,Chalet_no INTEGER NOT NULL
)
go
ALTER TABLE Teams
ADD CONSTRAINT Teams_pk PRIMARY KEY (Groupid)
ALTER TABLE Accommodation2
ADD CONSTRAINT Accommodation2_fk PRIMARY KEY (Chalet_no)
ALTER TABLE Booking
ADD CONSTRAINT bookingid_pk PRIMARY KEY (Groupid, Chalet_no)
ALTER TABLE Booking
ADD CONSTRAINT Groupid_fk FOREIGN KEY (Groupid) REFERENCES Teams (Groupid)
ALTER TABLE Booking
ADD CONSTRAINT Chalet_no_fk FOREIGN KEY (Chalet_no) REFERENCES Accommodation2 (Chalet_no)
go
-- Example of declarative referential integrity
-- directly in each CREATE TABLE statement:
DROP TABLE Booking
DROP TABLE Teams
DROP TABLE Accommodation2
CREATE TABLE Teams
(GroupID INTEGER NOT NULL
,CONSTRAINT pk_Teams PRIMARY KEY (GroupID)
)
go
CREATE TABLE Accommodation2
(ChaletNo INTEGER
,CONSTRAINT pk_Accommodation2 PRIMARY KEY (ChaletNo)
)
go
CREATE TABLE Booking
(GroupID INTEGER NOT NULL
,ChaletNo INTEGER NOT NULL
,CONSTRAINT pk_Booking PRIMARY KEY (GroupID, ChaletNo)
,CONSTRAINT fk_Booking_Teams_GroupID
FOREIGN KEY (GroupID)
REFERENCES Teams (GroupID)
,CONSTRAINT fk_Booking_Accommodation2_ChaletNo
FOREIGN KEY (ChaletNo)
REFERENCES Accommodation2 (ChaletNo)
)
go
|
|
|
|
yuanyelss
Starting Member
China
4 Posts |
Posted - 02/19/2011 : 00:03:37
|
| unspammed |
Edited by - yuanyelss on 02/19/2011 00:10:58 |
|
|
|
jcelko
Esteemed SQL Purist
USA
547 Posts |
Posted - 02/19/2011 : 17:35:30
|
quote:
Originally posted by Drucey
Hi everyone, new to SQL and working on an assignment, I am creating a table with an attribute of gender ..
You do know that we will hunt down your professors and tell them you are presenting the work of others as your own for a class? Most colleges will expel you, as they should, you little cheat.
You don't do research, which means you are a bad student. There is an ISO Standard for sex_code (0 = unknown, 1 = male, 2 = female, 9 = lawful person (corporations organization, etc). You don't know that columns are not fields. You don't know the ISO-8601 temporal standards.
--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL
|
|
|
|
Drucey
Starting Member
United Kingdom
5 Posts |
Posted - 02/24/2011 : 08:56:13
|
| I'm not actually copying and presenting any work as my own, simply asking people in the know, which is no different to asking a friend in the profession ect. I ask, learn how its done and then i do it myself which is perfectly legal. As for research forums and such are an encouraged form. As long as i get information, LEARN it and apply it myself i am not sure what is wrong here? So feel free to tell my lecturers and explain how i asked a couple things. Thanks to everyone for the input |
|
|
| |
Topic |
|
Constraints
