CODEWITHSUNDEEP
sqldatabasepostgresql
64bit_twitchyliquid
64bit_twitchyliquid

Reputation: 924

Can a SERIAL foreign key be set to null?

Question: I have a table with a couple of foreign keys. I want to make two of the foreign keys optional (that is, they can be set to NULL). When I try to run the command to create the table, I get the error:

ERROR: conflicting NULL/NOT NULL declarations for column "activityid" of table "eventassociation"

If I try and create the table without the NULL declaration for the foreign keys, those foreign keys implicitly become NOT NULL.

How can I make it so that the foreign keys activityID and roleID can be null?

Thanks,

hypoz

Extra information:

I am running postgreSQL (version on apt-get) on ubuntu 12.04.

This is the problematic table:

CREATE TABLE eventAssociation (
  associationID SERIAL PRIMARY KEY,
  studentID VARCHAR(12) references volunteers(studentID),
  eventID SERIAL references events(eventID),
  activityID SERIAL references activities NULL,
  roleID SERIAL references roles(roleID) NULL,
  coordinatorConfirmed BOOLEAN,
  userRevokeable BOOLEAN
)

And for reference, here is the schema for the activities table. The roles table is pretty much identical except with different field names.

CREATE TABLE activities (
  activityID SERIAL PRIMARY KEY,
  eventID integer references events(eventID),
  name varchar(128),
  description TEXT
)

Upvotes: 4

Views: 3333

Answers (1)

user330315
user330315

Reputation:

Defining a column that is a FK as serial does not make any sense. It means that whenever you don't supply a value, a new id will be generated - I cannot imagine any situation where that makes sens.

I think you actually want to define those columns as integer: 

CREATE TABLE activities 
(
  activityID    SERIAL PRIMARY KEY,
  eventID       INTEGER REFERENCES events,
  name          VARCHAR(128),
  description   TEXT
);

CREATE TABLE eventAssociation 
(
  associationID          SERIAL PRIMARY KEY,
  eventID                INTEGER REFERENCES events,
  activityID             INTEGER REFERENCES activities,
  coordinatorConfirmed   BOOLEAN,
  userRevokeable         BOOLEAN
);

Upvotes: 9

Related Questions